How to set Read Committed in YugabyteDB
Tags: yugabytedb, postgres, isolation, sql
Published: 2022-10-31 13:25:30

YugabyteDB is [PostgreSQL compatible] (https://www.yugabyte.com/tech/postgres-compatibility/), 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:

```sh
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:

```sql
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`:

```sh
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:
```sql
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.