Information needed on locks

Question

Post reply

Information needed on locks

VRT

SSCertifiable

Points: 5776
More actions
December 19, 2008 at 12:39 am

#197618

Hi,
How the SQL Server applies locks on tables?
I have 3 cases, I want to know how the SQL Server 2005 behaves in these 3 scenarios?
Let us assume user1 and user2 are the users.
they are accessing table1.
1) user1 and user2 selecting the data from table1.
2) User1 selecting table1 and user2 updating table1.
3) User1 and user2 updating the table1.
How the SQL server applies locks on these cases and if no locks then it will be a trouble na... if it applies locks then the case 3 leads to a dead lock situation. how does it react on this case?
Thank You

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Why would (3) lead to a deadlock?

Have you read through the sections in Books Online on locking?

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

ps. One Orange Chip Points: 29422 More actions · Answer 2

1) user1 and user2 selecting the data from table1.

Shared locks will be applied and each user will be able to see results. The locks get expired once all requested data is pulled and displayed to the user.

2) User1 selecting table1 and user2 updating table1.

(if user1 come first, he'd acquire a shared lock preventing user2 to update the table; if user2 comes first, user1 will have to wait until updation is complete.

3) User1 and user2 updating the table1.

This should not cause deadlock as the user coming 2nd will have to wait until 1st finishes with his update and releases the lock.

Deadlock will happen if user1 has aquired a lock on table1 and is waiting for lock to be released on table2; where as at the same time, user2 has acquired lock on table2 and is waiting for locks to be released on table1.

Pradeep Singh

VRT SSCertifiable Points: 5776 More actions · Answer 3

Please let me know any online material links on Transacation management and lock management, I was so confused with other databases and with SQL Server? I will refresh my knowledge once.

Thank You

ps. One Orange Chip Points: 29422 More actions · Answer 4

Refer BOL and msdn...

http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Pradeep Singh

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

I think the one thing you're missing is, by and large, you don't need to worry about lock management. SQL Server manages locks for you. You do need to understand how it manages locks just so you can write your code appropriately and avoid deadlock situations (not #3, as has already been noted). In addition to reading up on locks and blocks, make sure you read up on transaction isolation levels because they affect how SQL Server goes about managing locks.

"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