CODEWITHSUNDEEP
javajdbclockingdatabase
Luigi Massa Gallerano
Luigi Massa Gallerano

Reputation: 2357

How does JDBC Transaction lock a table in TRANSACTION_READ_COMMITTED isolation level?

I've read this tutorial: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html but I think I'm still missing something.

Let us take an example:

So, these Threads perform a Transaction (setAutoCommit(false) on two different Connections). This Transaction executes, on a single DB Table, the following queries:

The Progressive Number must be unique (it is Primary Key) in this Table.

Do JDBC Transactions (with TRANSACTION_READ_COMMITTED isolation level) avoid the problem that T1 and T2 read the same value of PN and both try to insert in the table the same PN++? Is the Table locked until the Insert is performed and the commit() is called?

Upvotes: 0

Views: 11633

Answers (2)

Kristiaan
Kristiaan

Reputation: 406

You could also synchronize on the application level. This would work, provided that only one method in one application is doing the increment.

private **synchronized** void increment() {
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);
    int current = dao.getMaxNumber(year);
    dao.insertNumber(year, current+1);
}

Upvotes: 0

Germann Arlington
Germann Arlington

Reputation: 3353

No, in order to make sure that you always have unique number you will need to: 1) [better] change the DB field to identity/sequence/auto-number depending on DB 2) use UUID as identifier 3) [worst] lock the row for the duration of read/increment/write sequence

TRANSACTION_READ_COMMITTED will only make sure that you can read ONLY the data that is already committed to DB. I.e. if you had another 200 DB operations between your 

UPDATE sequence

and 

commit

other threads would not be able to read the data that you updated until you commit, so in fact it will do quite the opposite of what you want.

Upvotes: 1

Related Questions