This section explains how explicit locking works in YugabyteDB.
Explicit row-locks use transaction priorities to ensure that two transactions can never hold conflicting locks on the same row. This is done by the query layer assigning a very high value for the priority of the transaction that is being run to acquire the row lock, causing all other transactions that conflict with the current transaction to fail (because they have a lower value for the transaction priority). YugabyteDB supports most row-level locks, similar to PostgreSQL.
NoteExplicit locking is an area of active development in YugabyteDB. A number of enhancements are planned in this area. Unlike PostgreSQL, YugabyteDB uses optimistic concurrency control and does not block / wait for currently held locks, instead opting to abort the conflicting transaction with a lower priority. Pessimistic concurrency control is currently under development.
The types of row locks currently supported are:
FOR NO KEY UPDATE
FOR KEY SHARE
Let us look at en example with the
FOR UPDATE row lock, where we first
SELECT a row for update to lock it and subsequently update it. A concurrent transaction should not be able to abort this transaction by updating the value of that row after the row is locked. To try out this scenario, first create an example table with sample data as shown below.
yugabyte=# CREATE TABLE t (k VARCHAR, v VARCHAR); yugabyte=# INSERT INTO t VALUES ('k1', 'v1');
Next, connect to the cluster using two independent
ysqlsh instances called session #1 and session #2 below.
NoteYou can connect the session #1 and session #2
ysqlshinstances to the same server, or to different servers.
|session #1||session #2|
Begin a transaction in session #1. Perform a `SELECT FOR UPDATE` on the row in the table `t`, which will end up locking the row. This will cause the row to get locked for an update as a part of a transaction which has a very high priority.
Before completing the transaction, try to update the same key in `session #2` using a simple update statement. This would use optimistic concurrency control, and therefore would fail right away. If `session #1` had used optimistic concurrency control instead of an explicit row-lock, then this update would succeed in some of the attempts and the transaction in `session #1` would fail in those cases.
NoteBlocking this transaction and retrying it after the other transaction completes is work in progress.
Update the row and commit the transaction in `session #1`. This should succeed.