CODEWITHSUNDEEP
javamysqljdbclocking
Itai
Itai

Reputation: 6911

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

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.

Upvotes: 1

Views: 6594

Answers (3)

Michael Kanios
Michael Kanios

Reputation: 59

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.

Upvotes: 2

Sabir Khan
Sabir Khan

Reputation: 10142

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 !!

Upvotes: 2

Jaydatt
Jaydatt

Reputation: 142

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.

Upvotes: 0

Related Questions