transaction isolation level: set table isolation level differently

Question

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?

This question lacks context. Too broad on its current form. Please give context to your question and add a minimal, complete, verifiable example of what you want [(help)](https://stackoverflow.com/help/mcve) — Alfabravo, Mar 20 '18 at 20:39

Bill Karwin · Accepted Answer · 2018-03-21T00:07:41.073

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.

MySQL: AUTO_INCREMENT
Oracle: SEQUENCE
Microsoft SQL Server: IDENTITY

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.

Thanks. So the JPA id generator using table is a design flaw because it will not work in concurrency (multi-threading and concurrent transactions). — eastwater, Mar 21 '18 at 00:11
@Sunnyday, Right, using `GenerationType.TABLE` is not a good idea, because it requires table locking to *prevent* concurrency. Forcing concurrent sessions to act serially is the only way that generation strategy can avoid the race condition. But there are better strategies. Read https://www.thoughts-on-java.org/jpa-generate-primary-keys/ and https://stackoverflow.com/questions/4102449/how-to-annotate-mysql-autoincrement-field-with-jpa-annotations — Bill Karwin, Mar 21 '18 at 00:26

transaction isolation level: set table isolation level differently

1 Answers1