How do I lock database records from Java?

Question

I'm developing a database program in Java with dbf. I need to know how to lock the database records from the Java side.

Example: we have a database table cheques with 5 records in the table (record 1 through 5). There are 2 users, user-1 and user-2. user-1 accesses record 1 and user-2 tries to access record 1 at the same time. I want to lock record 1 to prevent access to that record by user-2 while user-1 is accessing it. How do I do this in Java?

Sermonize

MySQL takes care of contention for you: dev.mysql.com/doc/refman/5.0/en/internal-locking.html that is one of the advantages of using a relational DB instead of rolling your own data store. What is the problem you are having? – Buffalo

MySQL does it for you. – Yorkist

Such locking will be needed only if MySQL built-in locking is not sufficient. Please specify how you are accessing the database - JDBC, JPA, something else? – Sclera

my accessing method is sun.jdbc.odbc.JdbcOdbcDriver. My requirement is I need to notify for user-2 from my java swing application when user-2 try to access record 1 while user-1 accessing it. – Sermonize

Answer 1

In case of MySQL, you can use, SELECT FOR UPDATE statement for locking the records. Lock will not be released until the transaction completes or rolls back. More on the same here.

Onia

how do i do that. Please advice me coz i'm new to this forum. – Sermonize

On the left side of the answer, you fill find a Tick mark button. Just click on it. It will be filled with green color. Following link demonstrates the same: stackoverflow.com/help/accepted-answer – Onia

Answer 2

It depends on the environment you are working on. for a container managed transaction your container manages the transactions for you and all you have to do is to set the Lockmode to lockmode.write. What this does is that it blocks all write access to the class methods while userA is accessing record 1. On the other hand for a stand alone application you can just add Synchronization key word to your method to control concurrent access. I hope this helps.

Autogenesis

Answer 3

Not every database supports per-record locking.

Generally, if you are in EE environment, you can use JPA EntityManager#find() method to lock certain record.

Full usage will be like that

// EntityManager em;
YourClass obj = em.find(YourClass.class, primaryKey, LockModeType.WRITE);
// do something
em.merge(obj);

After transaction commit the record(s) will be released.

In non-EE environment, as Darshan Mehta said, connection.createStatement().execute("SELECT * FROM table FOR UPDATE") will be the solution.

Judgeship