December 13, 2011 at 4:33 pm
I have an insert query that explicitly takes a tablelock on a table it is selecting from. e.g:
INSERT INTO tablea(columns...) SELECT (columns) FROM tableb with (tablockx)
I do know "tableb" can have many inserts done a second (one record at a time) and is about 1 million rows.
Can anyone shed some light as to why this might be? Is it possible that with READ COMMITTED isolation there would be too many concurrent transactions for the table to be fully read in a reasonable amount of time?
December 13, 2011 at 7:20 pm
Not sure what exactly you're asking, but I think that's it is possible you have unrealistic expectations of what READ COMMITTED does for you. If that is the case, this article may help to clarify.
December 13, 2011 at 8:06 pm
My question is simply why would someone use a tablockx hint as was done in the query I gave.
My comment about READ COMITTED isolation was because I do not know the impact of selecting an entire table (just under a million rows) that is constaintly heavily modified. If there were many row-level X locks on the table from other processes doing INSERT or UPDATE would that not slow down a query selecting all the rows from that table (as opposed to READ UNCOMMITED or a WITH NO LOCK hint)? And if so would explicitly requesting a tablockx speed up the select?
December 13, 2011 at 8:32 pm
raistlinx (12/13/2011)
If there were many row-level X locks on the table from other processes doing INSERT or UPDATE would that not slow down a query selecting all the rows from that table (as opposed to READ UNCOMMITED or a WITH NO LOCK hint)? And if so would explicitly requesting a tablockx speed up the select?
There are a number of possibilities here. Perhaps whomever added the hint found that it 'resolved' a deadlocking problem. Maybe they found that the SELECT was taking a large number of row-level locks. This is not the default behaviour of READCOMMITTED; shared locks are usually released as soon as the data is read, so the number of locks does not accumulate. There are occasions, however, where shared locks are taken and held to the end of the statement - I would need to see a query plan to be sure about that. It could also be that the reason for the TABLOCKX is to take a consistent snapshot of the table at a point in time.
The fact that it is a TABLOCKX (exclusive table lock) instead of TABLOCK (shared table lock) might suggest the target was deadlocking, but who can say for sure. It could equally be that the person concerned simply misread where an exclusive table lock should be placed to enable minimally-logged INSERTs in SQL Server 2008.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 14, 2011 at 4:39 am
Hi Paul, thanks for pointing me in a few directions. As far as I know this is an auto-generated query but I will be meeting with the developers today to find out more.
If we assume for a minute that deadlocking wasn't part of the reason, say it was just to get a snapshot in time. In theory would you expect this query to perform much worse than a similar one not taking an exclusive lock on the table? Or is it too hard to say?
December 14, 2011 at 5:20 am
raistlinx (12/14/2011)
If we assume for a minute that deadlocking wasn't part of the reason, say it was just to get a snapshot in time. In theory would you expect this query to perform much worse than a similar one not taking an exclusive lock on the table? Or is it too hard to say?
The query taking the exclusive lock could be faster (possibly much faster) if it is able to acquire the single exclusive table lock quickly, because acquiring a large number of row or page locks is an expensive operation. On the other hand, if the query is blocked by concurrent activity for any length of time, it could be much slower (in execution time).
It'll be interesting to hear what the developers say.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 15, 2011 at 7:50 pm
A belated thanks for the feedback. The developers feel it is an auto-generated query (they wern't sure). I've set up a more detailed session next week to review all the code executed by the server this query came from. I'll post back if anyting interesting comes from it.
Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply