Derby doesn't allow isolation level to be set at TRANSACTION_SERIALIZABLE for concurrent access

Question

I'm using Derby database and trying to create DB objects concurrently.

As we all know that the default isolation level in it is TRANSACTION_READ_COMMITTED.

But I don't want to allow even phantom reads with the DB and hence, I want to set the isolation level to TRANSACTION_SERIALIZABLE.

All I do is this bit of code.

if(jdbcTemplate.getDataSource().getConnection().getTransactionIsolation() == Connection.TRANSACTION_READ_COMMITTED) {
                logger.info("The connection isolation is already TRANSACTION_READ_COMMITTED");
            }
            else {
                logger.info("Please set the connection isolation to TRANSACTION_READ_COMMITTED");
            }

            jdbcTemplate.getDataSource().getConnection().setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            if(jdbcTemplate.getDataSource().getConnection().getTransactionIsolation() == Connection.TRANSACTION_SERIALIZABLE) {
                logger.info("The connection isolation is now TRANSACTION_SERIALIZABLE");
            }

As expected, I see the log as "The connection isolation is already TRANSACTION_READ_COMMITTED", but surprisingly I dont see the log for "The connection isolation is now TRANSACTION_SERIALIZABLE".

Also the exception that I was expecting occurs which confirms that the isolation level was not set to Serializable.

Is there a question here? Did you get an exception? What was the exception? What where you trying to achieve, and what have you tried? — Bryan Pendleton, May 03 '13 at 14:11
Basically, I create some threads and then insert objects into database. I want to make sure that each thread creates unique id (We have an IdGenerator that will check existing id and will create a new one). So I wanted part of the transaction to not allow phantom reads. For that I wanted to keep isolation level to TRANSACTION_SERIALIZABLE. But it doesn't work as of yet. I get an org.springframework.dao.DuplicateKeyException — Prayag, May 03 '13 at 14:33

score 0 · Answer 1 · answered Nov 13 '18 at 09:55

 1 if(jdbcTemplate.getDataSource().getConnection().getTransactionIsolation() == Connection.TRANSACTION_READ_COMMITTED) {
 2                logger.info("The connection isolation is already TRANSACTION_READ_COMMITTED");
 3            }
 4            else {
 5                logger.info("Please set the connection isolation to TRANSACTION_READ_COMMITTED");
 6            }
 7
 8            jdbcTemplate.getDataSource().getConnection().setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 9            if(jdbcTemplate.getDataSource().getConnection().getTransactionIsolation() == Connection.TRANSACTION_SERIALIZABLE) {
10                logger.info("The connection isolation is now TRANSACTION_SERIALIZABLE");
11            }
12

I know this is an old thread, but I stumbled upon it and wanted to mention, that as I understand it, the calls to getConnection() @ lines 1,8,9 do not all reference the same connection at all -- so there is no reason to expect that changing the isolation of one connection will change the isolation of the subsequent ones.

Instead, if you actually keep a reference to the connection and then use that in your code, you should see the expected result...

 0 Connection conn = jdbcTemplate.getDataSource().getConnection();
 1 if(conn.getTransactionIsolation() == Connection.TRANSACTION_READ_COMMITTED) {
 2                logger.info("The connection isolation is already TRANSACTION_READ_COMMITTED");
 3            }
 4            else {
 5                logger.info("Please set the connection isolation to TRANSACTION_READ_COMMITTED");
 6            }
 7
 8            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 9            if(conn.getTransactionIsolation() == Connection.TRANSACTION_SERIALIZABLE) {
10                logger.info("The connection isolation is now TRANSACTION_SERIALIZABLE");
11            }
12

Derby doesn't allow isolation level to be set at TRANSACTION_SERIALIZABLE for concurrent access

1 Answers1