sqldatabasejpatransaction-isolationid-generation

transaction isolation level: set table isolation level differently

If I set transaction isolation level to READ_COMMITTED, can I set a table isolation level differently such as READ_UNCOMMITTED? The reason for this is that the changes to a table need to be visible immediately to other transactions.

Transaction: READ_COMMITTED
Table Foo: READ_UNCOMMITTED

For example, JPA table id generator

     Entity Type      Next Id
----------------------------------
     EMP                100
     DEPT               5

When one transaction gets a new Id for Employee, increase its Id to 101. This new id must be visible to other transactions immediately. Otherwise it will cause duplicate Id.

Suppose the isolation levels of all transactions are READ_COMMITTED. How to make the changes to the table visible to other transactions before committing current transaction?

How about Mysql, Oracle db, SqlServer?

Solution

  • This new id must be visible to other transactions immediately. Otherwise it will cause duplicate Id.

    Using READ_UNCOMMITTED will not solve your problem.

    Suppose your transaction read the latest id value, and then attempts to use id+1. But in between the moments of reading the last id and attempting to use the next value, some third transaction used that value, and then you've still got a duplicate error.

    This is called a race condition, and even if you use READ_UNCOMMITTED, you cannot solve it by being quick.

    Besides, it's a bad idea to use READ_UNCOMMITTED in any database. What if the other transaction is rolled back for some reason? Your transaction will have read data that will never "exist" in the sense that it was never committed.

    Exactly for this reason, all brands of RDBMS have a feature to generate id values outside of transaction scope.

    What each of these implementations has in common is that the database can generate new id values in a kind of global scope, so that two concurrent transactions are guaranteed not to generate the same value.

    You must use the mechanism to generate unique id values, and not depend on SELECT id+1... solution. SELECT is subject to transaction isolation, so it cannot see the most recently generated id value, whether committed or not. But the database knows, and it will never return the same value to two different transactions.

    It's unfortunate that the syntax of each of these implementations is different, so it's hard to write SQL code that works the same on all brands of SQL database.