December 19, 2008 at 12:39 am
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
December 19, 2008 at 12:52 am
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
December 19, 2008 at 12:54 am
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.
December 19, 2008 at 1:55 am
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
December 19, 2008 at 2:02 am
Refer BOL and msdn...
http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
December 19, 2008 at 6:03 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply