0

I have one question regarding transaction isolation level and locking in mysql.

I have one query keeps getting the lock timeout error:

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

The queries basically try to delete records with a specific date_id. BTW, the date_id is indexed as well. But most of the times, there are no records match, i.e. it will delete nothing.

After some investigation, I found it may be caused by a culprit long-running query, which do a range select on the same table. However, what confused me is both transaction run at isolation level of "READ COMMITTED". So I have no clue why a lock is needed and why it could timeout (especially consider there is no matching record to delete)

Thank you in advance!

Alfred
  • 1,709
  • 8
  • 23
  • 38
  • Use `SHOW INNODB STATUS` to see what kind of SQL block yours ? – Gea-Suan Lin Dec 06 '14 at 09:44
  • Do you use InnoDb tables, or MyIsam tables ? On MyIsam writers block readers and vice versa, a simple SELECT statement place o lock on all tables involved in the query, and block writers. – krokodilko Dec 06 '14 at 11:17

1 Answers1

2

The transaction isolation "read committed" is a contract: the database promises to only read committed data and keep not-yet-committed data out of the transaction. The lock timeout error is a runtime-error: the database tries to update data but it cannot find a good moment to do so (see the "innodb_lock_wait_timeout" mentioned here in the MySQL reference manual). Even if there is no data to delete, the database needs to find a moment in time to assert that.

The transaction isolation "read committed" already improves the database's chances of finding a good moment to update data (see here for example), but it cannot prevent other queries/transactions from locking the entire table ("full table scan" like your culprit query probably does).

Some more searching does show a possible solution for your delete problem.

Community
  • 1
  • 1
vanOekel
  • 6,358
  • 1
  • 21
  • 56