How to lock database records in a Java EE application?

Question

I want to write a Java EE web application where different users work with a database. A user can start editing a record, and then either save changes or cancel editing. While the user is editing, the record should be locked for other users. It should be locked on the database level, because there are also other non-Java users editing the same database, locking the records they work on.

I understand some basic Java + databases, but I am not good at multiple-user things like locking. Looking for some examples on the internet, it seems to me like every "hello world" example for a Java EE technology introduces at least one another technology. To access objects in the database, I use JPA. To lock records, I probably need transactions, which brings JTA. To work with JTA, I need JNDI. To work with all those objects, I probably also need EJB and injections... and at this moment I wonder whether this is really the most simple way to solve the problem, or whether I missed something important. I do not know whether all those technologies are necessary (if yes, I will use them; I just would like to be sure before I learn them all). I just see that the examples I found on the web introduce them very generously.

I would like a simple example of a Java EE code which:

• uses JPA;

• connects to a database described in the "persistence.xml" file;

• has a MyObject class with properties id and name, stored in the MYOBJECT table;

• has a method (e.g. called from a JSP page) that database-level locks the object with id = 42 (so that non-Java users with access to the same database also cannot modify it), or displays an error if the record is already locked by another user (either another Java user, or a non-Java user);

• has another method (e.g. called from another JSP) that either updates the name to a specified value and releases the lock, or just releases the lock if empty string is provided.

For each new technology you introduce in the solution, I would like to hear a very short explanation why did you use it. Also whether that technology requires me to install new libraries, create or modify configuration files, write additional code, etc. (The JSP files which call the methods are not necessary; I am interested in the database-related parts.)

(Another detail: Here is described a difference between EntityTransaction and UserTransaction. If I understand it correctly, JTA is needed only if I use multiple databases. Is it also necessary if I use only one Oracle database with different schemas? If yes, the please write the example code using JTA.)

Answer 1

1) If you want to lock a record in a database, you need something called pessimistic lock. Remember this keyword and use it for further googling. Simply said, pessimistic lock means really locking the record in the database. Which means that if your Java application makes a pessimistic lock, the record is really locked; so even if some other non-Java program accesses the same database, the record will be locked, and they cannot modify it.

On the other hand, the so-called optimistic lock is mostly a pretend-lock. It is, approximately, a "we most likely don't need to lock this record anyway, so we will not really lock it, and if something bad happens, then we will try to fix the problem afterwards" approach. Which actually makes sense and increases performance, but only in situations where the assumptions behind this approach are true; where the conflicts are really rare, and where you really can fix the problem afterwards. Unless you understand it well (which you don't seem to), just don't use it.

2) JPA is a unified approach for using a database with transactions and stuff, and it also maps objects to tables for you. This is probably what you want.

JTA is the same stuff, plus a unified approach to use transactions over many databases, so it is more powerful than JPA, but that means it has additional functionality that you don't really need. On the other hand, for using these superpowers you pay some cost, like losing the ability to start and transactions on whim. The server will manage the transactions for you, as the server needs. If you completely understand how exactly that works, then you know whether this fits your needs; but if you don't, then you rather avoid it. Your development environment may offer you JTA as a default option, but that is only because it thinks that you are going to write Skynet. By not using JTA you also don't have to use JNDI, EJB, and many other Skynet-related technologies.

3) After hearing this, now it is time for you to do your homework. Because now you have an idea of what to do. Read the "javax.persistence" API documentation.

You can use annotated Java classes to represent your database tables; or you can use the old-fashioned SQL queries; or both, as you wish. You can use either of them to lock and release records. A lock must be inside of a transaction, so if you want to keep the lock, you have to keep the transaction.

Answer 2

We will not solve this for you. You are asking for everything. You need to code it your self, but here is a link for JPA locking.

Hint: Use @Version

Read here for information on locking for JPA