Locking in VB.NET application

[Job Lot]

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

[Nick Malik]

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?

[Job Lot]

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?

[Nick Malik]

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?