CODEWITHSUNDEEP
javamysqllockingrowstatus
PaulFrancis
PaulFrancis

Reputation: 5819

Check MySQL table's ROW LOCK STATUS via Java

I have a Java frontend and a MySQL backend scenario, I used a 'LOCK IN SHARE MODE' for SELECT. If I request the same row from another process, it gives the data.. However it does not allow me to update. What I would like to do is inform the user they will only have a READ only copy, so if they wish to see the information they can else they can request it later.. How could I check the status of the ROW so that the user will be informed about this situation?? If I use 'FOR UPDATE', It just waits for until the first user saves the data. I find it less user friendly, if they just have a blank screen or when they click button it does nothing. Any help will be greatly appreciated. Using MySQL 5.5, Java 7.

Upvotes: 2

Views: 2938

Answers (2)

antak
antak

Reputation: 20789

The question's entire premise lies in the rather liberal use of RDBMS' row-level locking (which is usually used for short-lived concurrency control) directly for interactive UI control.

But putting that aside and answering the question, one can set the session's innodb_lock_wait_timeout to a very short value, minimum being 1, and catching the resulting Lock wait timeout exceeded; try restarting transaction when unable to lock.

The exception class was com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException when I just tried with mysql-connector-java 5.1.38, but other exception classes has changed over releases so this too may be different in older version of MySQL Connector/J.

The "attempt and fail" method of acquiring locks is the standard way of tackling these types of concurrency situations, as the alternate method of "check before attempting" is an anti-pattern that creates a race-condition between checking and the actual attempt to lock.

Upvotes: 0

Mehran
Mehran

Reputation: 16851

The short answer is "You can't"!

You may want to take a look at this discussion.

[EDIT]

The answer to that post states:

You can't (check lock's state) for non-named locks!!!! More info: http://forums.mysql.com/read.php?21,222363,223774#msg-223774

Row-level locks are not meant for application level locks. They are just means to implement consistent reads and writes. That means you have to release them as soon as possible. You need to implement your own application level lock and it's not that much hard. Perhaps a simple user_id field will do. If it is null then there's no lock. But if it's not null, the id indicates who is holding the record. In this case you'll need row-level locking to update the user_id field. And as I said before, you'll have to release MySQL lock as soon as you are done locking / unlocking the record.

Upvotes: 1

Related Questions