473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Locking in VB.NET application

How can I lock records in VB.NET application using ADO.NET?


Jul 21 '05 #1
3 2955
There's rarely a need to.
First off, take a look at:
http://msdn.microsoft.com/library/de...cyChecking.asp

and then read this
http://msdn.microsoft.com/library/de...oncurrency.asp

As it is probably clear by now, pessimistic locking largely causes more
problems than it is worth and most folks avoid it.

My personal favorite is what is called the "Version Number Approach" in the
first article, because SQL Server supports it natively with the "timestamp"
data type. When you query a row that contains a Timestamp, that column
returns with a long integer, fairly random value. (Not useful for tracking
time, despite it's name). You update your record data in memory. You begin
a transaction. You call an "update" stored procedure and send all of the
fields, including the timestamp, back to it. This proc retrieves the row by
it's primary key and compares the timestamp value. If the value is
different, then the row was updated by someone else. The SP raises an
error, that .NET traps. The .NET app rolls back the transaction. You
inform the user that the data in the row changed, and ask if they want to go
get the original record from the database again (thus keeping the other
person's change) or if they want to simply ignore the other person's change
and overwrite it. If the latter, you call the same stored proc, but pass a
zero for the timestamp value. Your stored proc knows that a zero means
"ignore any changes and overwrite at all costs."

It works. What can I say. I don't use the "overwrite at all costs" method
very often... On one project, where contention was a common, and fairly bad,
thing, I went considerably farther. I would get the changed row, compare it
to the original row (before the user changed it) and for any fields that
were different, create a single pop-up window showing the user the changed
fields and ask if they wanted to apply the changes to their data for the
contended fields. In other words, I helped the user merge their changes.

I hope this helps.

--- Nick
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:C5**********************************@microsof t.com...
How can I lock records in VB.NET application using ADO.NET?


Jul 21 '05 #2
Nick it was gr8. Just a quick question, if I take "Version Number Approach" I’ll have to append a new column in every table, I am having more that 50 tables in my database for this project. Is it worth making that change over using “Saving All Values Approach”?

Thanx

"Nick Malik" wrote:
There's rarely a need to.
First off, take a look at:
http://msdn.microsoft.com/library/de...cyChecking.asp

and then read this
http://msdn.microsoft.com/library/de...oncurrency.asp

As it is probably clear by now, pessimistic locking largely causes more
problems than it is worth and most folks avoid it.

My personal favorite is what is called the "Version Number Approach" in the
first article, because SQL Server supports it natively with the "timestamp"
data type. When you query a row that contains a Timestamp, that column
returns with a long integer, fairly random value. (Not useful for tracking
time, despite it's name). You update your record data in memory. You begin
a transaction. You call an "update" stored procedure and send all of the
fields, including the timestamp, back to it. This proc retrieves the row by
it's primary key and compares the timestamp value. If the value is
different, then the row was updated by someone else. The SP raises an
error, that .NET traps. The .NET app rolls back the transaction. You
inform the user that the data in the row changed, and ask if they want to go
get the original record from the database again (thus keeping the other
person's change) or if they want to simply ignore the other person's change
and overwrite it. If the latter, you call the same stored proc, but pass a
zero for the timestamp value. Your stored proc knows that a zero means
"ignore any changes and overwrite at all costs."

It works. What can I say. I don't use the "overwrite at all costs" method
very often... On one project, where contention was a common, and fairly bad,
thing, I went considerably farther. I would get the changed row, compare it
to the original row (before the user changed it) and for any fields that
were different, create a single pop-up window showing the user the changed
fields and ask if they wanted to apply the changes to their data for the
contended fields. In other words, I helped the user merge their changes.

I hope this helps.

--- Nick
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:C5**********************************@microsof t.com...
How can I lock records in VB.NET application using ADO.NET?



Jul 21 '05 #3
Surely, out of 50 tables, you have less than eight where contention would
actually occur or would be an issue! I've programmed many systems with more
than 50 tables, and contention would normally only occur in less than 10% of
them. If that statistic holds true, I'd expect that you would only modify
five tables.

Think about it. If you have a lookup table used for drop-down lists that is
being changed, contention is not an issue because there's probably not more
than one or two fields that have any content at all.

Normally, contention is an issue for records that have a long active
lifespan. Therefore, order headers and order details are not contentious,
because once the order is filled, the record is effectively archived. On the
other hand, the customer record itself may be contentious because you would
(hopefully) have a long relationship with the customer.

I don't know what your application is. If it is typical of most business
applications, this kind of analysis should be fairly simple to do, and will
produce the small set of tables you would be concerned with.

For every other table (without the timestamp column), let the last change
win.

--- Nick

"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:96**********************************@microsof t.com...
Nick it was gr8. Just a quick question, if I take "Version Number Approach" I'll have to append a new column in every table, I am having more
that 50 tables in my database for this project. Is it worth making that
change over using "Saving All Values Approach"?
Thanx

"Nick Malik" wrote:
There's rarely a need to.
First off, take a look at:
http://msdn.microsoft.com/library/de...cyChecking.asp
and then read this
http://msdn.microsoft.com/library/de...oncurrency.asp
As it is probably clear by now, pessimistic locking largely causes more
problems than it is worth and most folks avoid it.

My personal favorite is what is called the "Version Number Approach" in the first article, because SQL Server supports it natively with the "timestamp" data type. When you query a row that contains a Timestamp, that column
returns with a long integer, fairly random value. (Not useful for tracking time, despite it's name). You update your record data in memory. You begin a transaction. You call an "update" stored procedure and send all of the fields, including the timestamp, back to it. This proc retrieves the row by it's primary key and compares the timestamp value. If the value is
different, then the row was updated by someone else. The SP raises an
error, that .NET traps. The .NET app rolls back the transaction. You
inform the user that the data in the row changed, and ask if they want to go get the original record from the database again (thus keeping the other
person's change) or if they want to simply ignore the other person's change and overwrite it. If the latter, you call the same stored proc, but pass a zero for the timestamp value. Your stored proc knows that a zero means
"ignore any changes and overwrite at all costs."

It works. What can I say. I don't use the "overwrite at all costs" method very often... On one project, where contention was a common, and fairly bad, thing, I went considerably farther. I would get the changed row, compare it to the original row (before the user changed it) and for any fields that
were different, create a single pop-up window showing the user the changed fields and ask if they wanted to apply the changes to their data for the
contended fields. In other words, I helped the user merge their changes.
I hope this helps.

--- Nick
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:C5**********************************@microsof t.com...
How can I lock records in VB.NET application using ADO.NET?



Jul 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Geoffrey | last post by:
We have developed a python class that can read data files created from another application. These target files are C-ISAM files used for accounting applications so the "primary" application may be...
3
by: Ryan | last post by:
I have a problem with record locking / blocking within an application. The app is quite straight forward. Written in Delphi 5 using BDE to access a SQL 7 database (Win2K server). Every so often...
9
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to...
16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
375
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
1
by: Fardude | last post by:
ACCESS 97, Pessimistic Record Locking!??? Does Access 97 allow record level Pessimistic locking? In other words, when user A is editing a record (has it locked) and User B tries to edit it...
0
by: brijeshmathew | last post by:
Hi I use Visual Basic 6, Service Pack 6, Microsoft ActiveX Data Objects 2.8 Library(msado15.dll) and access 2000 database using JET 4 OLE. I have an application that adds records simultaneously...
15
by: z. f. | last post by:
Hi, i have an ASP.NET project that is using a (Class Library Project) VB.NET DLL. for some reason after running some pages on the web server, and trying to compile the Class Library DLL, it...
9
by: master | last post by:
Actually, it is not only the record locking, what I need, and nobody seems to descibe this. Imagine the following scenario. There is a database with, say 10000 records with some unvalidated...
0
by: Cindy Huyser | last post by:
I have an Access 2000 database behind a threaded Java application that that can have have concurrent access to the same table (but not the same record). The database is set up for shared access...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.