Franck PachotYugabyteDB is PostgreSQL compatible, which means that it supports all transaction isolation level, with the same behavior. In previous versions, the Read Committed isolation level was mapped to the higher Repeatable Read level, and this is still the default in the current version (2.14). Here is an example where I start YugabyteDB with all default tserver flags:
docker run --rm yugabytedb/yugabyte:2.14.4.0-b26 bash -c '
yugabyted start --tserver_flags="" --listen 0.0.0.0
cat /root/var/logs/{master,tserver}.err
until postgres/bin/pg_isready ; do sleep 0.1 ; done | uniq &&
ysqlsh -e <<SQL
show yb_effective_transaction_isolation_level;
set default_transaction_isolation="read committed";
show yb_effective_transaction_isolation_level;
SQL
'
Here even when read committed is set at PostgreSQL level (the default), the yb_effective_transaction_isolation_level effective shows repeatable read. This read-only parameter was added to query easily the effective isolation level:
Starting yugabyted...
server_version
---------------------
11.2-YB-2.14.4.0-b0
(1 row)
show yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
repeatable read
(1 row)
set default_transaction_isolation="read committed";
SET
show yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
repeatable read
(1 row)
However, when enabling the true Read Committed semantic, with the yb_enable_read_committed_isolation flag when starting the tserver:
docker run --rm yugabytedb/yugabyte:2.14.4.0-b26 bash -c '
yugabyted start --tserver_flags="yb_enable_read_committed_isolation=true"
cat /root/var/logs/{master,tserver}.err
until ysqlsh -c "show server_version" ; do sleep 1 ; done 2>/dev/null
ysqlsh -e <<SQL
show yb_effective_transaction_isolation_level;
set default_transaction_isolation="read committed";
show yb_effective_transaction_isolation_level;
SQL
'
The effective isolation level is Read Committed:
Starting yugabyted...
server_version
---------------------
11.2-YB-2.14.4.0-b0
(1 row)
show yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
read committed
(1 row)
set default_transaction_isolation="read committed";
SET
show yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
read committed
(1 row)
When you are porting an application written for PostgreSQL, you probably want this because Read Committed is the only isolation level that doesn't require a retry logic for serializable errors. This is also why the databases who do not support this level, saying that higher isolation is better, are not PostgreSQL compatible: porting an application to it would require many changes in application design and code.
Why isn't yb_enable_read_committed_isolation=true the default? The reason is backward compatibility, because this was implemented recently, but there are good chances that it become the default soon. The commands in this blog post can help you do know exactly which is the effective isolation level for a specific version.
