I am starting to port our Wildfly code that used “vanilla” PostgreSQL to now use YugabyteDB instead, and I am running into the following problem:
Suppose we have a J2EE bean called Manager, that has a method methodX() with annotation @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED), a methodY() without any annotation, such that methodY() performs UPDATE on a database table, and a methodZ() similar to methodY() but which performs DELETE on the same record of the database table as methodY(). methodX() performs some logic that does not access the database and afterwards calls methodY(). If from within a method clientMethod() of a Client bean that does not have any annotation we call methodX() on the Manager bean and after that we attempt to call methodZ() on the Manager bean, then we get an error:
com.yugabyte.util.PSQLException: ERROR: Operation failed. Try again
By turning on the appropriate logging options on the PostgreSQL server being run by YugabyteDB, I see that the clientMethod() had started a transaction T1 before calling methodX(), then when the methodY() is called a new transaction T2 is started, and when methodZ() is called the transaction T1 is used.
This worked without any issues under “vanilla” PostgreSQL, so I would like to know if there are any configuration options we need to change from the defaults on the YugabyteDB yb-tserver or the PostgreSQL server that YugabyteDB runs for this to work on YugabyteDB, or if such functionality is not supported.
The expected behavior is for methodZ() to use the existing transaction T1.
Using the “vanilla” PostgreSQL logs as a reference, at the end of the clientMethod() (i.e., after the methodZ() is called), the transaction T1 is committed, and immediately afterwards the transaction T2 (that had been apparently waiting for transaction T1 to either commit or rollback) is committed as well.
From further investigation I found that while the default isolation level is “READ COMMITTED” in PostgreSQL, in YugabyteDB it is “Snapshot” (i.e., the equivalent of “REPEATABLE READ” in PostgreSQL). Also, in YugabyteDB the “READ COMMITTED” isolation level is by default mapped to “Snapshot”, unless the yb_enable_read_committed_isolation flag is set to true, in which case the “real” “READ COMMITTED” isolation level is supported. So, I set this flag, and also set ysql_default_transaction_isolation=‘READ COMMITTED’, so that the same isolation level is used as in PostgreSQL. Having done this, my scenario works in YugabyteDB as well without any errors.
However, I am concerned that I am using the READ COMMITTED isolation level, for which YugabyteDB has made some effort to keep “hidden” unless someone explicitly asks to use it. I also saw in https://docs.yugabyte.com/preview/architecture/transactions/isolation-levels a statement that the “Snapshot” isolation level is considered to be a good default for a distributed SQL database.
So, my question is whether it is not a good idea to use READ COMMITTED isolation level in YugabyteDB, and if so, why.
Hi, the main reason to support Read Committed is to be compatible with the default in PostgreSQL which, as all defaults, is the most commonly used. Higher isolation levels prevent more anomalies, and then are preferable if application can use them.
Here is an example of write consistency anomaly in PostgreSQL: READ COMMITTED anomalies in PostgreSQL - DEV Community
We have the same in YugabyteDB, for compatibility, but you can prevent that with higher isolation level, like in PostgreSQL.
Note that, to prevent anomalies, higher isolation level may encounter more retry-able exceptions, that can happen at the end of the transaction (commit)