Row level locking

Question

Row level locking

mshah23

SSChasing Mays

Points: 649
More actions
June 22, 2010 at 6:48 am

#103951

I would like to implement Oracle "FOR UPDATE" row level concept on SQL Server 2005.
I tried using the UPDLOCK with ROWLOCK, however I found that it open a lock on the table itself and you can not query the table for other rows as well.
First Session I execute below sql using "BEGIN TRAN"
select STATCD from w03u999s with(updlock, rowlock) where STATCD='DTDERRORED'
In Second session I executed below sql using "BEGIN TRAN"
select STATCD from w03u999s with(updlock, rowlock) where STATCD='EXPPRONEED'
My Second session got on waiting condition, however I am selecting a different row "'EXPPRONEED'".
Can anyone please help me out while creating Row level locking. Thanks in advance.
June 22, 2010 at 7:17 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 1

By and large in SQL Server you don't want to try to control locking. In general it does row level locking by default, all on it's own.

Why are you trying to take direct control of the locking? If you don't have a specific problem or functional purpose to issue locking commands, you're better off not doing it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mshah23 SSChasing Mays Points: 649 More actions · Answer 2

Basically On UI screen, if user select any case(Radio Button) for modication and start any modification that particualt case needs to be locked for other user.

That's why I want to have a lock starting from SELECT.

I think I have found someting.

begin tran

SET LOCK_TIMEOUT 0

select queuecd from w03u999s WITH (updlock, rowlock) where queuecd='NBAGGCNT'

After setting the LOCK_TIMEOUT and using WITH (updlock, rowlock) hint I am able to stop second session to even select the same row.

Does any one has any concern using this approach?

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

mshah23 (6/22/2010)
Basically On UI screen, if user select any case(Radio Button) for modication and start any modification that particualt case needs to be locked for other user.
That's why I want to have a lock starting from SELECT.
I think I have found someting.
begin tran
SET LOCK_TIMEOUT 0
select queuecd from w03u999s WITH (updlock, rowlock) where queuecd='NBAGGCNT'
After setting the LOCK_TIMEOUT and using WITH (updlock, rowlock) hint I am able to stop second session to even select the same row.
Does any one has any concern using this approach?

Enormous problems. Locking from the client is a very dangerous and problematic approach to take within SQL Server. I would stronly suggest you read through the documentation on how locking works within SQL Server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mshah23 SSChasing Mays Points: 649 More actions · Answer 4

What is the problem do you see on the above solution?

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

mshah23 (6/22/2010)
What is the problem do you see on the above solution?

Just as a starting point, what happens if, within the transaction you're holding open, you need to read more rows? That can run into locks held by other processes, and those locks won't be released by you because you're holding them open, this can cause deadlocks or just simple blocking.

How about what happens when the connection is dropped through an error process and, for whatever reason, isn't cleaned up appropriately, which happens sometimes. The locks are still retained.

I'll see if I can't get others to comment on it as well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 6

The database is a shared resource. You don't want to unnecessarily use, or lock, resources that might be needed. What is the user goes to lunch and forgets to release the record?

The way that this is handled in many other systems, like networks and operating systems, is that you raise a semaphore. That way any other application that wants to access that resources knows it is in use. In SQL Server we would typically do this by adding a flag (bit or tinyint) to the tables, and setting it to 1 to indicate someone has the record locked. You could also make it a varchar() and record the username/hostname if you wanted.

SQL Server does not make what you are looking to do easy since it causes additional overhead that isn't necessary to handle concurrent access.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Grant Fritchey (6/22/2010)
I'll see if I can't get others to comment on it as well.

What happens if the user goes for lunch leaving the screen open, then decides to take the rest of the day off. Records locked and inaccessible to anyone else for the entire day. Plus, seeing as there's an open transaction, no log truncation for the rest of the day, a growing transaction log, maybe even an out of space error if the admin doesn't notice it fast enough.

Transactions should NEVER be left open during user integration. Performance problems, locking, transaction log growth.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 8

GilaMonster (6/22/2010)
Grant Fritchey (6/22/2010)
I'll see if I can't get others to comment on it as well.
What happens if the user goes for lunch leaving the screen open, then decides to take the rest of the day off. Records locked and inaccessible to anyone else for the entire day. Plus, seeing as there's an open transaction, no log truncation for the rest of the day, a growing transaction log, maybe even an out of space error if the admin doesn't notice it fast enough.
Transactions should NEVER be left open during user integration. Performance problems, locking, transaction log growth.

Darn it, darn it, darn it. That was the one I was trying to think of that came up the last time this discussion was opened. I need a new brain.

Thanks, Gail.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

mshah23 (6/22/2010)
I would like to implement Oracle "FOR UPDATE" row level concept on SQL Server 2005.

I'm sure you're aware that the same issues Gail and Grant pointed out apply to Oracle as well.

It was a poor solution in Oracle, it would be a poor solution in SQLServer.

-- Gianluca Sartori

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

Grant Fritchey (6/22/2010)
Darn it, darn it, darn it. That was the one I was trying to think of that came up the last time this discussion was opened. I need a new brain.

Sorry, the new brain records are locked by another transaction...:hehe::-P

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

jcrawf02 (6/22/2010)
Grant Fritchey (6/22/2010)
Darn it, darn it, darn it. That was the one I was trying to think of that came up the last time this discussion was opened. I need a new brain.
Sorry, the new brain records are locked by another transaction...:hehe::-P

Maybe removing the READPAST hints in your brain's memory retrieval routines would help. 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 12

What I'd suggest in this scenario is for the client to take the data required, and store the original data values. When the user issues a 'commit' at the UI, the current data is checked against the original - if there's a difference and this means that the commit will overwrite changes made in the meantime, a warning to this effect is raised at the UI informing the user of the issue. The user can then choose to abandon, continue with the refreshed data or overwrite as appropriate.

spaghettidba SSC Guru Points: 105732 More actions · Answer 13

That's called "optimistic locking".

ADO and other DB libraries implement this technique natively.

-- Gianluca Sartori