When is `SELECT ... FOR UPDATE` lock released?

Question

I am working on a program that allows multiple users to access a db (MySQL), and at various times I'm getting a SQLException: Lock wait timeout exceeded .

The connection is created using:

conn = DriverManager.getConnection(connString, username, password);
conn.setAutoCommit(false);

and the calls all go through this bit of code:

try { 
    saveRecordInternal(record);
    conn.commit();
} catch (Exception ex) {
    conn.rollback();
    throw ex;
}

Where saveRecordInternal has some internal logic, saving the given record. Somewhere along the way is the method which I suspect is the problem:

private long getNextIndex() throws Exception {
    String query = "SELECT max(IDX) FROM MyTable FOR UPDATE";
    PreparedStatement stmt = conn.prepareStatement(query);

    ResultSet rs = stmt.executeQuery();
    if (rs.next()) {
        return (rs.getLong("IDX") + 1);
    } else {
        return 1;
    }
}

This method is called by saveRecordInternal somewhere along it's operation, if needed. For reasons that are currently beyond my control I cannot use auto-increment index, and anyway the index to-be-inserted is needed for some internal-program logic.

I would assume having either conn.commit() or conn.rollback() called would suffice to release the lock, but apparently it's not. So my question is - Should I use stmt.close() or rs.close() inside getNextIndex? Would that release the lock before the transaction is either committed or rolled back, or would it simply ensure the lock is indeed released when calling conn.commit() or conn.rollback()?

Is there anything else I'm missing / doing entirely wrong?

Edit: At the time the lock occurs all connected clients seem to be responsive, with no queries currently under-way, but closing all connected clients does resolve the issue. This leads me to think the lock is somehow preserved even though the transaction (supposedly?) ends, either by committing or rolling back.

score 2 · Answer 1 · edited May 23 '17 at 10:27

Even though not closing a Statement or ResultSet is a bad idea but that function doesn't seem responsible for error that you are receiving. Function , getNextIndex() is creating local Statement andResultSet but not closing it. Close those right there or create those Statement and ResultSetobjects in saveRecordInternal() and pass as parameters or better if created in your starting point and reused again and again. Finally, close these when not needed anymore ( in following order - ResultSet, Statement, Connection ).

Error simply means that a lock was present on some DB object ( Connection, Table ,Row etc ) while another thread / process needed it at the same time but had to wait ( already locked ) but wait timed out due to longer than expected wait.

Refer , How to Avoid Lock wait timeout exceeded exception.? to know more about this issue.

All in all this is your environment specific issue and need to be debugged on your machine with extensive logging turned on.

Hope it helps !!

Thank you. So, if I understand correctly, using `stmt.close()` would _not_ release the lock until the transaction is either committed or rolled-back? — Itai, Dec 24 '15 at 07:34

score 2 · Answer 2 · answered Dec 24 '15 at 07:52

2

From the statements above I don't see any locks that remain open! In general MySql should release the locks whenever a commit or rollback is called, or when the connection is closed.

In your case

SELECT max(IDX) FROM MyTable FOR UPDATE

would result in locking the whole table, but I assume that this is the expected logic! You lock the table until the new row is inserted and then release it to let the others insert. I would test with:

   SELECT IDX FROM MyTable FOR UPDATE Order by IDX Desc LIMIT 1

to make sure that the lock remains open even when locking a single row.

If this is not the case, I might be a lock timeout due to a very large table.

answered Dec 24 '15 at 07:52

Michael Kanios

59
6

How large is very large? Currently it has <10K rows, and 6 columns (the largest of which is a `varchar(100)`, the others being int, date or `varchar(15)`) – Itai Dec 24 '15 at 07:57
That is actually very small, but that really depends from your environment. If Max(IDX) is not needed in your application, you could call this query at the time you actually insert the new row. – Michael Kanios Dec 24 '15 at 08:02
One more thing to consider in this case is that Select Max(IDX) From MyTable FOR UPDATE will actually perform a row level lock on all affected rows (in this case all). In might be a better idea to lock the whole table with: LOCK TABLES MyTable READ and then UNLOCK it! – Michael Kanios Dec 24 '15 at 09:44
Will using `Order by IDX Desc LIMIT 1` only lock the top row, or all of them as well? – Itai Dec 24 '15 at 11:01
I'm not sure of the exact implementation in MySQL! I would expect it to lock only the returned row, but I might actually lock all rows returned, based on the conditions. In this case there is no where clause, therefore it depends on the internal implementation. This is actually an interesting test to perform :) – Michael Kanios Dec 24 '15 at 11:52

score 0 · Answer 3 · answered Dec 24 '15 at 07:25

0

So, what I think happen here is: you query is trying to executed on some table but that table is locked with some another process. So, till the time the older lock will not get released from the table, your query will wait to get executed and after some time if the lock will no release you will get the lock time out exception.

You can also take a look on table level locks and row level locks. In brief: table level locks lock the whole table and till the lock is there you want be able to execute any other query on the same table. while row level lock will put a lock on a specific row, so apart from that row you can execute queries on the table.

you can also check if there is any query running on the table from long time and due to that you are not able to execute another query and getting exception. How, to check this will vary upon database, but you can google it for your specific database to find out query to get the open connections or long running queries on database.

answered Dec 24 '15 at 07:25

Jaydatt

142
7

Yes, I figured there is a lock somewhere, the question is - how it comes to stay locked? All clients seem to finish their tasks, but the lock remains (I'll edit my question to explain this). – Itai Dec 24 '15 at 07:27
So, what may happen here is: client has executed some query on database, and client released the connection. but the query has started executed on database. So, even though client has released the connection the query is still running. – Jaydatt Dec 24 '15 at 07:29
That doesn't make sense, because when closing the client the problem is solved, so it can't be an orphaned connection/query. – Itai Dec 24 '15 at 07:36
which simply says that the code has some loop hole which keeps the client connection open. – Jaydatt Dec 24 '15 at 07:37

When is `SELECT ... FOR UPDATE` lock released?

3 Answers3