YugabyteDB 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.
Top comments (2)
Thanks for sharing, Franck.
For some reason I had issues trying to start the Yugabyte database up via Docker while setting the
tserver_flagswith "yb_enable_read_committed_isolation=true". The container simply didn't start as expected 😖. Anyway, I will do some tests later following this article 😊😊Just out of curiosity, which relational databases don't support
READ_COMMITTED? 👀In the code above, I've added
cat /root/var/logs/{master,tserver}.errafter theyugabyted startso that error is displayed if the server does not start. This can help troubleshooting. A bad flag will show an error.Many distributed databases support only Serializable, like CockroachDB or MariaDB Xpand. It is ok for new applications, as they can implement a retry logic. But to be PostgreSQL compatible, YugabyteDB implemented all isolation levels with same behavior.