Pessimistic locking, Read Committed, and all Isolation Levels

Optimistic Concurrency Control looks attractive in a distributed system because it avoids locks. However, in an SQL database, this approach means that conflicts are detected at commit time, leading to the application's need for transactions to be rolled back and retried. This can degrade performance and increase the amount of non-business logic code.

SQL developers often opt for pessimistic locking, which involves waiting on conflicts to avoid receiving a retryable error. They typically choose the Read Committed isolation level because it allows each query to run with its own read time and automatic retries. In YugabyteDB, an implicit savepoint is created before each statement in the Read Committed isolation level, and the statement is transparently restarted in case of a race condition conflict. Developers can still avoid non-repeatable reads in the Read Committed isolation level by using SELECT FOR UPDATE, which waits on conflicts. Alternatively, they can ignore the locked row with SKIP LOCKED or fail on conflict with NOWAIT.

yugabyte=# begin transaction;
BEGIN

yugabyte=*# update demo set counter=counter+1 where id=1 returning *;
 id | counter
----+---------
  1 |       1
(1 row)

UPDATE 1

yugabyte=*# -- another session doing the same:
yugabyte=*# \! psql -c 'update demo set counter=counter+1 where id=1 returning *' & sleep 1

yugabyte=*# -- commit the first one, the other session continues without failing
yugabyte=*# commit;
COMMIT

yugabyte=#  id | counter
----+---------
  1 |       2
(1 row)

UPDATE 1

YugabyteDB implements a wait-on-conflict pessimistic concurrency control with wait queues, deadlock detection, and statement restart in Read Committed. It also supports a fail-on-conflict behavior in higher isolation levels, like Repeatable Read, which avoids phantom reads without locking, and Serializable, which avoids all anomalies, including write skew. Read-only workloads are optimized with SERIALIZABLE DEFFERABLE to avoid all anomalies without locks and serializable errors.

Note: for backward compatibility, Read Committed is not enabled by default. The cluster nodes must be started with --yb_enable_read_committed_isolation=true. Here is how you can check the effective isolation level:

yugabyte=# show yb_effective_transaction_isolation_level ;

 yb_effective_transaction_isolation_level
------------------------------------------
 read committed

(1 row)

Concurrency control | YugabyteDB Docs

Details about Concurrency control in YSQL

docs.yugabyte.com