November 30, 2011 at 5:15 pm
After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of
1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]
2. TABLOCK vs. TABLOCKX
3. XLOCK
Thanks.
December 1, 2011 at 5:07 am
Lexa (11/30/2011)
After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]
2. TABLOCK vs. TABLOCKX
3. XLOCK
Thanks.
In general, don't use hints. They are there as a last resort way to take control away from SQL Server because you're in some odd circumstance.
1) Usually, I've used update locks when I'm reading from a table that I also intend to update later in the query. This takes out locks in such a way that if another process is also reading from the table, it is unable to get a lock and must wait for mine to clear, thus avoiding deadlock situations.
2) Shared lock taken on the whole table vs. Exclusive lock taken on the whole table
3) It means that all locks taken are exclusive locks. So a row lock is instead an exclusive row lock, etc.
Are you studying or trying to solve a specific problem?
"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
December 2, 2011 at 1:52 pm
Grant Fritchey (12/1/2011)
Lexa (11/30/2011)
After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]
2. TABLOCK vs. TABLOCKX
3. XLOCK
Thanks.
In general, don't use hints. They are there as a last resort way to take control away from SQL Server because you're in some odd circumstance.
1) Usually, I've used update locks when I'm reading from a table that I also intend to update later in the query. This takes out locks in such a way that if another process is also reading from the table, it is unable to get a lock and must wait for mine to clear, thus avoiding deadlock situations.
2) Shared lock taken on the whole table vs. Exclusive lock taken on the whole table
3) It means that all locks taken are exclusive locks. So a row lock is instead an exclusive row lock, etc.
Are you studying or trying to solve a specific problem?
Thanks. I'm trying to troubleshoot some code written by prev. DBA but in general I try not to use hints.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply