Monday, January 11, 2010

If a user has executed an insert/update on a table, but not committed the transaction, other users will find that their queries on the same table hang. This is because the table has been locked and the first user's process is blocking everyone else's.

The following scenario illustrates this. User Bob updates a table but does not commit it. User Alice then tries to query the table, but here command hangs:

In this example, Alice's query to the table hangs because Bob has not committed his transaction. You can use the sp_who command in order to see which commands are blocked and who they are being blocked by.

The output shows that spid 343 from MACHINE21501 is being blocked by spid 51 from MACHINE21302. You can use the command sp_lock 51 in order to find more information about the locking process.

You can either kill the blocking process (if you have DBA rights) using kill 51 or use the psloggedon utility to find out which user is logged onto MACHINE21302, so that you can tell them to commit their open transaction.

Subscribe to fahd.blog

Hi, I'm Fahd, a software developer at an investment bank in London. I am passionate about technology and work mainly with open source software, specialising in Java applications and Unix-based operating systems.

This blog is a place for me to share useful code snippets to solve problems that I have come across, and to write about ideas and experiences as a programmer.

All code on this blog has been written by me, unless stated otherwise, and you are free to use, share and adapt it for any purpose, under the terms of the GNU General Public License.

I love hearing back from my readers, so please feel free to leave comments! Thanks for reading and happy programming :-)