Why does a SELECT wait for a lock?

Question

In my application I have the problem that sometimes SELECT statements run into a java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction exception. Sadly I can't create an example as the circumstances are very complex. So the question is just about general understanding.

A little bit of background information: I'm using MySQL (InnoDB) with READ_COMMITED isolation level.

Actually I don't understand how a SELECT can ever run into a lock timeout with that setup. I thought that a SELECT would never lock as it will just return the latest commited state (managed by MySQL). Anyway according to what is happening this seems to be wrong. So how is it really?

I already read this https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html but that didn't really give me a clue. No SELECT ... FOR UPDATE or something like that is used.

Do you use `LOCK TABLES` in another session? That will cause a `SELECT` to be blocked, and when it times out after `lock_wait_timeout` seconds, it will return the error you showed. — Bill Karwin, Mar 18 '19 at 14:52
The only other way I know you could get a timeout on `SELECT` (if there are no locking options) is if you're running an ALTER TABLE or other DDL on the table, and you have the `lock_wait_timeout` option set to a low value. If the `SELECT` can't get access to the table in that number of seconds, it times out with the error you showed. — Bill Karwin, Mar 19 '19 at 12:57
This should not be the case. I'll investigate further in the future but for now I'm fine with the point that a `SELECT` should not lock. Maybe I'll find more information in the future about my case. — Werzi2001, Mar 20 '19 at 09:50

score 1 · Answer 1 · answered Mar 18 '19 at 09:16

1

That is probably due to your database. Usually this kind of problems come from that side, not from the programming side that access it.In my experience with db's, these problems are usually due to that. In the end, the programming side is just "go and get that for me in that db" thing.

I found this without much effort.

It basically explains that:

Lock wait timeout occurs typically when a transaction is waiting on row(s) of data to update which is already been locked by some other transaction.

You should also check this answer that has a specific transaction problem, which might help you, as trying to change different tables might do the timeout

the query was attempting to change at least one row in one or more InnoDB tables. Since you know the query, all the tables being accessed are candidates for being the culprit.

answered Mar 18 '19 at 09:16

M.K

1,464
2
24
46

You're basically right but in my case I get the lock when using `SELECT`. It is clear to me how it works when changing stuff but how can I get a lock on `SELECT`? To my understanding this should not happen. – Werzi2001 Mar 18 '19 at 09:26
To my understanding, if it is locked when updating because it has been locked, it might work as well as with a select. It is trying to select a locked transaction, so it is waiting for it to unlock, therefore it locks. At least, that makes kind of sense to me... Have you checked **all** transactions made before that select? @Werzi2001 – M.K Mar 18 '19 at 09:33
As far as I know a "normal" write lock should not affect `SELECT` statements. I could also confirm that in small test cases. Checking *all* transactions is not really possible as it only happens during heavy load during production. So there might be hundreds of transactions being relevant. – Werzi2001 Mar 18 '19 at 10:06
I guess the problem might be exactly that... _there might be hundreads of transactions being relevant_ ... Probably a transaction has accessed to edit/update, and the select is waiting. Something similar to a `deadlock`. That is as far as my knowledge knows... @Werzi2001 Sorry for not being able to help you more than this. – M.K Mar 18 '19 at 10:10
No need to apologize. From my understanding so far a `SELECT` should not lock - no matter what other transactions do. But according to what @Lukas wrote that might be incorrect. – Werzi2001 Mar 18 '19 at 10:14
I see, both of us got to the dead-lock conclusion. I'll investigate more about this. If i get to something new, I'll make sure to let you know through here! @Werzi2001 – M.K Mar 18 '19 at 10:50

score 1 · Answer 2 · edited Mar 18 '19 at 21:32

To speed up queries in a DB, several transactions can be executed at the same time. For example if someone runs a select query over a table for the wages of the employees of a company (each employee identified by an id) and another one changes the last name of someone who e.g. has married, you can execute both queries at the same time because they don't interfere.

But in other cases even a SELECT statement might interfere with another statement.

To prevent unexpected results in a SQL transactions, transactions follow the ACID-model which stands for Atomicity, Consistency, Isolation and Durability (for further information read wikipedia).

Let's say transaction 1 starts to calculate something and then wants to write the results to table A. Before writing it it locks all SELECT statements to table A. Otherwise this would interfere with the Isolation requirement. Because if a transaction 2 would start while 1 is still writing, 2's results depend on where 1 has already written to and where not.

Now, it might even produce a dead-lock. E.g. before transaction 1 can write the last field in table A, it still has to write something to table B but transaction 2 has already blocked table B to read safely from it after it read from A and now you have a deadlock. 2 wants to read from A which is blocked by 1, so it waits for 1 to finish but 1 waits for 2 to unlock table B to finish by itself.

To solve this problem one strategy is to rollback certain transactions after a certain timeout. (more here)

So that might be a read on for your select statement to get a lock wait timeout exceeded.

But a dead-lock usually just happens by coincidence, so if transaction 2 was forced to rollback, transaction 1 should be able to finish so that 2 should be able to succeed on a later try.

That sounds reasonable although it makes me kind of sad. I thought the database would implement something like "Copy on Write" to preserve the old state until the new one is fully written and valid. — Werzi2001, Mar 18 '19 at 10:07
Can you provide steps to produce this effect using InnoDB? It's an MVCC implementation, in which writers should never block readers. I have not observed an UPDATE locking all SELECT statements as you describe. — Bill Karwin, Mar 18 '19 at 14:58
Sadly I can't provide an example as the whole system is very complex and even then it just happens only rarely. It is not reproducible. — Werzi2001, Mar 19 '19 at 12:43
I meant that comment for Lukas, to ask for an example of how one would show updates blocking concurrent reads in InnoDB. In my experience, that doesn't happen in InnoDB. — Bill Karwin, Mar 19 '19 at 12:49

Why does a SELECT wait for a lock?

2 Answers2