Opinions - with MS SQL Server - how do you lock?

[szlamany]

dee-u PM'd me last night and asked about locking in MS SQL Server when doing an action query like - UPDATE ... WHERE...

I'm looking for opinions and techniques.

How do you all LOCK rows or recordsets? Both INLINE SQL and STORED PROCEDURES.

Thanks in advance.

[techgnome]

I guess I've never really thought about it too much. Our user base isn't large enough at any one location for this to be a huge problem. If there is going to be a conflict in something, we use transactions to "lock" the tables.

Or are we talking about user A pulls up a record, and user B tries to pull up that same record? If that's the case. We don't lock it. Last save gets their data updated. And with the way our system works and the type of system that it is, that hasn't been a problem. And this is something our clients know right up front (it's covered in the demo.)

I'm not even sure how we would handle something like that. At least not with out some re-engineering.

Tg

[kaffenils]

I usually let SQL Server decide which is the best locking method (row...page...table).

Row level locking is the "best" method for maximum concurrency, but it eats memory like ... something huge and hungry. And then there is table level locking, which does the exact opposite of row level locking.

I have come to the conclusion that in most cases SQL Server does a great job in deciding locking levels.

[szlamany]

Here in our shop we basically do the same thing - let SQL decide how to lock.

I've always had huge fears about holding locks on a row by some client PC that the user goes to lunch - causing deadlock issues. I'm not sure how true those fears are.

We have one process - which adjudicates medical claims - that takes about 10 or so minutes to run. It's one STORED PROCEDURE - called by one of the users from the VB client program. An ADO STORED PROCEDURE call is an implicit transaction (I believe we have BEGIN TRAN/COMMIT in the SPROC anyway...)...

At any rate, this SPROC does process around 2000+ rows in the medical claim table - with SQL doing it's automatic locking, we do sometimes get hourglass waits on the other user PC's. This is expected - so not a lot we can do about it. There are other SPROCS that run in the several minute area - we've recently put some WITH (NOLOCK) hints in these SPROCS to help avoid locks when they run.

[szlamany]

Or are we talking about user A pulls up a record, and user B tries to pull up that same record? If that's the case. We don't lock it. Last save gets their data updated. And with the way our system works and the type of system that it is, that hasn't been a problem. And this is something our clients know right up front (it's covered in the demo.)

We basically do this as well - last user to update gets the data put into the row.

Are other people doing something besides this?

Still curious...

[vb_dba]

We let SQL Server handle our locking here as well, with one exception.

I do have a procedure where I lock a table that contains a "next" barcode number. We have multiple workstations accessing/updating the table, so in order to prevent then grabbing the same number, I lock the table, grab the value, then set the next number and release the lock. I don't have to worry about deadlocking here because the user doesn't control the lock, the procedure does.

[szlamany]

We let SQL Server handle our locking here as well, with one exception.

I do have a procedure where I lock a table that contains a "next" barcode number. We have multiple workstations accessing/updating the table, so in order to prevent then grabbing the same number, I lock the table, grab the value, then set the next number and release the lock. I don't have to worry about deadlocking here because the user doesn't control the lock, the procedure does.

Just curious - to clarify...

You mean in the STORED PROCEDURE you lock the table - grab the row - update the row - unlock the table - leave the STORED PROCEDURE?

[kaffenils]

... we do sometimes get hourglass waits on the other user PC's. This is expected - so not a lot we can do about it. There are other SPROCS that run in the several minute area - we've recently put some WITH (NOLOCK) hints in these SPROCS to help avoid locks when they run.

Couldn't you do BEGIN and COMMIT TRAN for each claim, unless of course the business logic requires that everything to be committed in the same transaction?

The NOLOCK (equivalent to READUNCOMMITTED) option you use sounds a little bit scary to me, as it can cause users (or the other SPROCS) to make decissions on dirty data.

[kaffenils]

We let SQL Server handle our locking here as well, with one exception.

I do have a procedure where I lock a table that contains a "next" barcode number. We have multiple workstations accessing/updating the table, so in order to prevent then grabbing the same number, I lock the table, grab the value, then set the next number and release the lock. I don't have to worry about deadlocking here because the user doesn't control the lock, the procedure does.

This is a good example where you should specify the locking hint yourself. I'm guessing you use TABLOCKX or a XLOCK hint.

[szlamany]

Couldn't you do BEGIN and COMMIT TRAN for each claim, unless of course the business logic requires that everything to be committed in the same transaction?

The NOLOCK (equivalent to READUNCOMMITTED) option you use sounds a little bit scary to me, as it can cause users (or the other SPROCS) to make decissions on dirty data.

This large SPROC returns a big-old recordset at the end that gets turned into the "Adjudication Register Report".

We have a question that the user answers before the run - "Do you want to commit?". They basically can get a "pre-check register" to look for errors. Also, if an adjudication error is encountered during the run, it automatically rollback's the transaction - but still returns the recordset (an abbreviated one - with just the "hard" error rows that need correction).

So yes - it's a business requirement that the entire run be in one transaction. I dislike long running transactions - but unfortunately this one has to stay

The NOLOCK hint was used sparingly - with threads posted here prior to it's implementation. The SPROC that uses it is ok with getting stale or uncommitted data - since it's determining eligibility for claims and will run every day anyway - so stale data by a day is not a big problem.

[szlamany]

This is a good example where you should specify the locking hint yourself. I'm guessing you use TABLOCKX or a XLOCK hint.

We avoid locking for situations like this by using a WHERE clause that only updates if the row is not changed by another user - here's an example SPROC (not a real one - just whipped it up)...

Create Procedure GetValue 
As
Declare @LastVal int
Declare @NextVal int

Do_Again:

Set @LastVal=(Select LastValue From SomeTable Where ConfItem='LastTrans')
  -- Get the last value from the config table

Set @NextVal=@LastVal+1
  -- Increment it by one

Update SomeTable Set LastValue=@NextVal
   Where ConfItem='LastTrans' and LastValue=@LastVal
  -- Update the row - but only if it's not changed by another user
  -- Shouldn't really be possible anyway since this SPROC is a "transaction"

If @@RowCount=0 Goto Do_Again
  -- If the ROWCOUNT=0 - meaning we did not update - must be because
  -- we need to do it again...

Select @NextVal "NextValue"
  -- Return the value

[Hack]

I guess I've never really thought about it too much. Our user base isn't large enough at any one location for this to be a huge problem. If there is going to be a conflict in something, we use transactions to "lock" the tables.

Or are we talking about user A pulls up a record, and user B tries to pull up that same record? If that's the case. We don't lock it. Last save gets their data updated. And with the way our system works and the type of system that it is, that hasn't been a problem. And this is something our clients know right up front (it's covered in the demo.)

I'm not even sure how we would handle something like that. At least not with out some re-engineering.

Tg

Ditto, ditto and ditto. This is the exact same scenerio as employeed in my shop.

[vb_dba]

Just curious - to clarify...

You mean in the STORED PROCEDURE you lock the table - grab the row - update the row - unlock the table - leave the STORED PROCEDURE?

I first update the row (contrary to my original post), placing a Table lock on the table while the update takes place. Once I've updated the table, I commit the transaction and select the value from the table.

This is a good example where you should specify the locking hint yourself. I'm guessing you use TABLOCKX or a XLOCK hint.

Update ... With (TABLOCK, HOLDLOCK) is what I use.

[vb_dba]

I have worked at a shop though where we actually did row level locking. They were C applications that connected to an Informix database. It used client-side cursors to lock the row so no one else could even view the record while it was being looked at. It worked very well, but I don't see a need for that where I'm at now.

[kaffenils]

Create Procedure GetValue 
As
Declare @LastVal int
Declare @NextVal int

Do_Again:

Set @LastVal=(Select LastValue From SomeTable Where ConfItem='LastTrans')
  -- Get the last value from the config table

Set @NextVal=@LastVal+1
  -- Increment it by one

Update SomeTable Set LastValue=@NextVal
   Where ConfItem='LastTrans' and LastValue=@LastVal
  -- Update the row - but only if it's not changed by another user
  -- Shouldn't really be possible anyway since this SPROC is a "transaction"

If @@RowCount=0 Goto Do_Again
  -- If the ROWCOUNT=0 - meaning we did not update - must be because
  -- we need to do it again...

Select @NextVal "NextValue"
  -- Return the value

Thats also a way of doing it. Never though of that approach. Clever

[si_the_geek]

I also keep locking as brief as possible, and let the server handle the lock type. For my systems there are relatively few users per app, so there aren't often noticeable issues at all (I think 3 in 5 years).


One difference from the methods mentioned so far, is that I let the first update through.

Any subsequent updates are temporarily rejected (basically returning a pre-defined fail code instead of the "Do_again" loop), and the user gets the option to overwrite the values that have been altered. The altered values are shown on a highlighted version of the edit screen, or a grid for larger data sets.

(admittedly in a couple of older systems, and a couple with large data sets, the last update always goes through)

[szlamany]

SI - what is the method you use to determine that the row was changed by another user, so that the subsequent update fails out?

[si_the_geek]

I've used various methods over the years, I started out using the methods provided in DAO/ADO recordsets (which give a specific error when you update).

I've since moved on to the following methods (depending on the DBMS & database structure):

a) if there are fields for "last update time", just compare that for the updated rows;
b) temp tables on the server to store the "old" data, which can be compared just before the update (obviously this can cause issues with larger data sets);
c) "old" data stored in the app, and returned in where clauses (only for single rows, and kept to a minumum due to the network load issues)

[szlamany]

When we migrated our data from the mainframes into MS SQL Server, we added a "TDATE" column to the end of each and every table. In all our SPROCS that do INSERT/UPDATE to tables, we set the TDATE=GETDATE()...

Our main goal of this is to track "reality" when a user calls in a bogus problem (oh - I changed that last week, how come it's just doing this now! or... I never touched that person!)...

I've always imagined that we could also use this field as SI suggests - make sure that an UPDATE includes in the WHERE clause a check to see that the TDATE field hasn't changed...

But we have yet to see a real need for that.

I hope we get some more opinions - I know that there are quite a few more SQL people our there!

[kaffenils]

There are other SPROCS that run in the several minute area - we've recently put some WITH (NOLOCK) hints in these SPROCS to help avoid locks when they run.

I just came to think of, and you probably know this allready, that SQL Server 2005 (whenever it is released) can solve this "problem" by using row versioning, also called snapshot isolation. There is of course a performance cost since SQL Server has to keep the last committed rows in the TempDB until the current transaction is committed or rolled back.

[erickwidya]

i'm know it's not relevant to this thread..but this link might help u to check for what process and the LOCK they hold
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

[kaffenils]

i'm know it's not relevant to this thread..but this link might help u to check for what process and the LOCK they hold
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Great work !! I will definately use it. Until now I have used EM to find out who is locking what and who is blocking who. REP POINTS for this one.

[dee-u]

I wish to barge now... What will happen with this scenario?

'User1: 05:06:01
adoConn.Execute "UPDATE Table1 SET Field1=A WHERE Field2=Y"
 
'User1: 05:06:02
adoConn.Execute "UPDATE Table1 SET Field1=B WHERE Field2=Y"

And this?

'User1: 05:06:01
adoConn.Execute "UPDATE Table1 SET Field1=A WHERE Field2=Y"
 
'User1: 05:06:01
adoConn.Execute "UPDATE Table1 SET Field1=B WHERE Field2=Y"

[pranoy]

In the 1st case, the second update will return '0 rows updated'
and
in the 2nd case both will do the update, the order will depend on which process aquires the lock first.

[techgnome]

In the 1st case, the second update will return '0 rows updated'

How do you figure? It should still get one row updated....


In our shop, in both cases, who ever got there last would get the update. I assume it's a User 1, User 2, where User 1 sets field to "A" while User 2 is setting the same data to "B".... first, our system is such that it is extremely unlikely to be happening (because if it is, then that means that two customer service people are on the phone at the same time to the same (client's) customer) but in the event it does, in both cases, odds are User 2 gets the update.

Tg

[szlamany]

Dee-u - did you mean for your example to have a FIELD1 in the UPDATE/SET and a FIELD2 in the WHERE clause?

We discussed earlier in the thread using a WHERE clause trick that included the UPDATE/SET field in the WHERE clause so only one user would update.

Otherwise, I agree with TG - both UPDATE's are going to run in the example you posted - regardless of the times they fire. If they fire at exactly the same millisecond, they will still both run.

[pranoy]

Sorry..TG u r right. I dont know what I was thinking when I wrote that.

[dee-u]

I've edited my sample, what would be the value of Field1 with each scenario I've given? Specially at the same millisecond, will it differentiate them through nanoseconds? And what if they are also executed at the same nanosecond?

[szlamany]

I've edited my sample, what would be the value of Field1 with each scenario I've given? Specially at the same millisecond, will it differentiate them through nanoseconds? And what if they are also executed at the same nanosecond?

With a SQL database there is nothing being executed at the same exact time. Everything is sequentially processed - every request for ACTION is put into the LOG file for processing. If the LOG file has two requests to update the same row in it - one will be first, and one will be second - that is the nature of the sequential processing of the LOG file.

If the LOG file indicates that the row the second UPDATE is attempting to touch is locked, the the second ACTION request in the LOG file will hold until the first request processes OR is will deadlock and timeout.

There is no such thing as two requests occuring at the same time. Remember that a single program called SQLSERVER.EXE is taking each ADO request for ACTION on the DATABASE and processing them sequentially.