Not committing a transaction side effect

user_5153690 0
Viewed 80

I have a MySQL transaction that continuously runs in a loop every second and does the following:

  1. BEGIN
  2. SELECT ... FOR UPDATE LIMIT 100;
  • application code early returns if (2) returns 0 rows
  1. UPDATE SET ....
  2. COMMIT

I am not explicitly closing the transaction when I early return in step 2. Are there any side effects I should be concerned about? I shouldn't be holding on to any locks because the SELECT did not return any rows. Do I need to be concerned about these unclosed transactions building up on the database somewhere? Do they timeout automatically?

transactions mysql
1 Answers

This is going to cause you grief.

When you return early in step 2, you really really need to send a ROLLBACK; command.

When you do not send the ROLLBACK instruction, MySQL will keep the table locked, especially if a great many rows were updated. This will cause SELECTs, INSERTs, UPDATEs, DELETEs to block, leading to bad performance.

Eventually, if your program keeps the connections open, you can hit transaction limits and your code will start blowing up, usually about 1,000 "hung" transactions. See https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/

user_4300620
Related