Hi NG,
I am looking for an opinion here. I am new to C# and SQL, being an old
COBOL hand. I have started into a conversion of an old COBOL ERP system. I
have a number of functions working now and it is time to decide how best to
deal with the issues of concurrency. The old COBOL programs used record
locking to prevent concurrency errors and that logic is already "on the
shelf." Most C# books and articles I have seen use the default optimistic
concurrency as a way around these issues. I have coded a few test examples
and they work fine, except that you may have to tell a user to "try again."
I want to pause at this point before I code about 100 tables to get the
opinion of experienced experts on the best way to move forward. I have
noticed in books on SQL that you can do record locking down to (in some
cases) row level. This would seem like the way I wish to go. Further, I
have stayed away from SQL "Transactio ns" to this point, because there is a
lot I need to learn about SQL. Now may be the time to learn how to use
them. For ease of conversion I have tended to use SQL like the COBOL
programs use files. The less that SQL does, the more this project looks
like a language translation job. However, I really do want to use C# best
practices least I have an unsupportable "kludge" on my hands when I am done.
Thanks for your advice,
Bob
Robert Schuldenfrei bo*@s-i-inc.com 3 2491
Robert,
Never, ever, EVER use locking. If you do, you will be commiting your
system to death. It will NOT be scalable, period. You will hit a glass
ceiling in terms of how many operations you can support, and it will not be
pretty.
While yes, with concurrency checking, you will have to tell the user to
"try again", it is better than having corrupt data in your database, or data
that the user enters based on other data that is stale. It's generally not
a good thing. For SQL Server based solutions, I recommend the use of
timestamps, and then modifying your SQL on updates and deletes:
update <table> set <field> = <value> where id = <id> and timestamp =
<timestamp>
The delete follows the same pattern:
delete from <table> where id = <id> and timestamp = <timestamp>
The reason you do this is that from code, you can see the number of
records affected. If the number of records affected is zero, then you know
that either the id does not exist, or that the timestamp has changed, and
you can inform the user as such.
Of course, you wrap this operation in a transaction (unless it is the
only operation you are performing, since all single statements are
serializable transactions in themselves) to ensure that when you touch the
record, no one else can either, for the duration of your operation.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m
"Robert Schuldenfrei" <bo*@s-i-inc.com> wrote in message
news:kElWc.2905 6$9d6.17415@att bi_s54... Hi NG,
I am looking for an opinion here. I am new to C# and SQL, being an old COBOL hand. I have started into a conversion of an old COBOL ERP system. I have a number of functions working now and it is time to decide how best to deal with the issues of concurrency. The old COBOL programs used record locking to prevent concurrency errors and that logic is already "on the shelf." Most C# books and articles I have seen use the default optimistic concurrency as a way around these issues. I have coded a few test examples and they work fine, except that you may have to tell a user to "try again."
I want to pause at this point before I code about 100 tables to get the opinion of experienced experts on the best way to move forward. I have noticed in books on SQL that you can do record locking down to (in some cases) row level. This would seem like the way I wish to go. Further, I have stayed away from SQL "Transactio ns" to this point, because there is a lot I need to learn about SQL. Now may be the time to learn how to use them. For ease of conversion I have tended to use SQL like the COBOL programs use files. The less that SQL does, the more this project looks like a language translation job. However, I really do want to use C# best practices least I have an unsupportable "kludge" on my hands when I am done.
Thanks for your advice,
Bob
Robert Schuldenfrei bo*@s-i-inc.com
Hi Nick,
OK, I promise NEVER to even CONSIDER locking. I have two new skills to
learn: 1/ Transactions and 2/ using timestamps. I had been using a
technique of old record / new record to determine if the record had been
changed on update or delete. Timestamps seem more elegant. Sadly, the book
I have been using for learning SQL is Petkovic, SQL Server 2000, A
Beginner's Guide. He mentions timestamps, but then goes forward to
explaining locking (and not to well I might add). Can you suggest a good
book that implements your suggested approach to concurrency that would cover
both the C# and the SQL issues?
Thank you,
Bob
Robert Schuldenfrei bo*@s-i-inc.com
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c om> wrote in
message news:eN******** *****@TK2MSFTNG P12.phx.gbl... Robert,
Never, ever, EVER use locking. If you do, you will be commiting your system to death. It will NOT be scalable, period. You will hit a glass ceiling in terms of how many operations you can support, and it will not
be pretty.
While yes, with concurrency checking, you will have to tell the user
to "try again", it is better than having corrupt data in your database, or
data that the user enters based on other data that is stale. It's generally
not a good thing. For SQL Server based solutions, I recommend the use of timestamps, and then modifying your SQL on updates and deletes:
update <table> set <field> = <value> where id = <id> and timestamp = <timestamp>
The delete follows the same pattern:
delete from <table> where id = <id> and timestamp = <timestamp>
The reason you do this is that from code, you can see the number of records affected. If the number of records affected is zero, then you
know that either the id does not exist, or that the timestamp has changed, and you can inform the user as such.
Of course, you wrap this operation in a transaction (unless it is the only operation you are performing, since all single statements are serializable transactions in themselves) to ensure that when you touch the record, no one else can either, for the duration of your operation.
Hope this helps.
Hi Bob:
This article is a good read:
Handling Data Concurrency Using ADO.NET
John Papa http://msdn.microsoft.com/msdnmag/is...s/default.aspx
--
Scott http://www.OdeToCode.com
On Mon, 23 Aug 2004 14:18:33 GMT, "Robert Schuldenfrei"
<bo*@s-i-inc.com> wrote: Hi Nick,
OK, I promise NEVER to even CONSIDER locking. I have two new skills to learn: 1/ Transactions and 2/ using timestamps. I had been using a technique of old record / new record to determine if the record had been changed on update or delete. Timestamps seem more elegant. Sadly, the book I have been using for learning SQL is Petkovic, SQL Server 2000, A Beginner's Guide. He mentions timestamps, but then goes forward to explaining locking (and not to well I might add). Can you suggest a good book that implements your suggested approach to concurrency that would cover both the C# and the SQL issues?
Thank you,
Bob
Robert Schuldenfrei bo*@s-i-inc.com
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c om> wrote in message news:eN******** *****@TK2MSFTNG P12.phx.gbl... Robert,
Never, ever, EVER use locking. If you do, you will be commiting your system to death. It will NOT be scalable, period. You will hit a glass ceiling in terms of how many operations you can support, and it will not be pretty.
While yes, with concurrency checking, you will have to tell the user to "try again", it is better than having corrupt data in your database, or data that the user enters based on other data that is stale. It's generally not a good thing. For SQL Server based solutions, I recommend the use of timestamps, and then modifying your SQL on updates and deletes:
update <table> set <field> = <value> where id = <id> and timestamp = <timestamp>
The delete follows the same pattern:
delete from <table> where id = <id> and timestamp = <timestamp>
The reason you do this is that from code, you can see the number of records affected. If the number of records affected is zero, then you know that either the id does not exist, or that the timestamp has changed, and you can inform the user as such.
Of course, you wrap this operation in a transaction (unless it is the only operation you are performing, since all single statements are serializable transactions in themselves) to ensure that when you touch the record, no one else can either, for the duration of your operation.
Hope this helps. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: aurora |
last post by:
Hello!
Just gone though an article via Slashdot titled "The Free Lunch Is Over: A
Fundamental Turn Toward Concurrency in Software"
http://www.gotw.ca/publications/concurrency-ddj.htm]. It argues that the
continous CPU performance gain we've seen is finally over. And that future
gain would primary be in the area of software concurrency...
|
by: Peter Foti |
last post by:
Simple question... which is better to use for defining font sizes and why?
px and em seem to be the leading candidates. I know what the general answer
is going to be, but I'm hoping to ultimately get some good real world
examples.
Fire away! :)
Regards,
Peter Foti
|
by: Suzanne |
last post by:
Hi All
I'm having problems getting my data adapter to throw a concurrency
exception with an INSERT command. I want to throw a concurrency
exception if an attempt is made to enter a row into tb_table when a
row with the same int_UID already exists in there.
Here is my stored procedure:
if not exists (select int_UID from tb_table where...
|
by: ardin |
last post by:
anyone please help, i am developing and application that uses the update
method of dataadapter (select, update, delete command was set by issuing
the OleDbCommandBuilder)in updating the database. using this method
readily answers the concurrency issue. however, i always read from
various posts that is is best to use stored procs with...
|
by: Robert Schuldenfrei |
last post by:
Dear NG,
I was about to "improve" concurrency checking with a Timestamp when I
discovered
that my current code is not working. After about a day of beating my head
against the wall, I am turning to the NG in hopes that someone can spot what
I am doing wrong.
Key to this technique working is the SQL UPDATE statement. It is designed
to...
| |
by: Homa |
last post by:
Hi,
I'm thinking what will happen if two users access a page at the same
time. If there are any local variable in the page, will this cause
concurrency problem?
Simarily, if this page need to call some functions, say
logic.doSomthing(), and the class logic is a singleton (for
simplicity), will there be problems?
|
by: corey.coughlin |
last post by:
Alright, so I've been following some of the arguments about enhancing
parallelism in python, and I've kind of been struck by how hard things
still are. It seems like what we really need is a more pythonic
approach. One thing I've been seeing suggested a lot lately is that
running jobs in separate processes, to make it easy to use the latest...
|
by: mfreeman |
last post by:
The minimal code (VB.NET 2003) needed to show this problem is shown
below. All I do is loop through the records in the table and update
them without making any changes. Out of 600 records, about 40 of them
throw Concurrency violation errors in the update, and my GetAllColErrs
function provides no output. In comparing the rows that throw...
|
by: Rotsey |
last post by:
Hi,
I have a Access 2003 talking to SQL 2005 express DB via
linked tables.
I also have .NET forms application that also talks to the
SQL DB.
I want to know how to handle concurrency in the .NET app.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |