DEV Community

Franck Pachot for YugabyteDB Distributed PostgreSQL Database

Posted on • Updated on

How to set Read Committed in YugabyteDB

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
'
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
'
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
rponte profile image
Rafael Ponte • Edited

Thanks for sharing, Franck.

For some reason I had issues trying to start the Yugabyte database up via Docker while setting the tserver_flags with "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? πŸ‘€

Collapse
 
franckpachot profile image
Franck Pachot

In the code above, I've added cat /root/var/logs/{master,tserver}.err after the yugabyted start so 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.