Normally fast update statement very slow or timing out (locking)

Henry Hwang -

If you are having a problem where UPDATE queries that are normally fast are running unexpectedly slow or timing out there may be an uncommitted explicit transaction, or a long running query that is preventing your update because it has taken out a lock on the row you are attempting to UPDATE.

To identify long running locking queries, while you're running your update statement in one session, open up a second session and run the following:

SELECT * 
FROM system.sessions
WHERE xid IN (SELECT DISTINCT( holder )
               FROM system.lockman)\G 

If a session shows up, this may be the one that is blocking your update and you can ask the user to commit or you can try and kill the session.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk