MySQL: What happen when an executing sql’s client is terminated?

Posted on

Question :

Executing long-time sql like

UPDATE ACC SET A = 'EE' WHERE A IS NULL

then I found I made a mistake, so I kill my client. When I restarted client and executed another sql, like

UDPATE ACC SET A = 'EE' WHERE ID = 1888

but I got error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

I searched this error and found it was caused by uncommitted transaction holding locks. My question is:

  1. Can close client connection stop sql execution?
  2. If quesion 1’s answer is NO, when sql query finished but client disconnected, will MySQL commit the result?

Answer :

I believe that killing the client won’t be noticed by the server until the server sends data to the client. That is, the server will finish the query.

COMMIT happens in different ways.

  • BEGIN; UPDATE...; <client dies> — has not gotten to COMMIT, so no commit.
  • autocommit=OFF ... UPDATE ... — same as above
  • autocommit=ON ... UPDATE...; <updated finished, so committed> (I think)

Leave a Reply

Your email address will not be published. Required fields are marked *