Transaction priorities

• YSQL

When using YugabyteDB with the Fail-on-Conflict concurrency control policy, higher priority transactions can abort lower priority transactions when conflicts occur. External applications may control the priority of individual transactions using the YSQL parameters yb_transaction_priority_lower_bound and yb_transaction_priority_upper_bound.

A random number between the lower and upper bound is picked and used to compute a transaction priority for the transactions in that session as explained in Transaction Priorities.

To view the transaction priority of the active transaction in current session, use the yb_get_current_transaction_priority function.

Examples

Create a YugabyteDB universe and open two separate ysqlsh connections to it.

Transaction priority between concurrent operations

Consider an example scenario of an maintaining a bank account. Create the accounts table and insert rows into it, as follows:

create table account
  (
    name text not null,
    type text not null,
    balance money not null default '0.00'::money,
    primary key (name, type)
  );
insert into account values
  ('kevin','saving', 500),
  ('kevin','checking', 500);

To set a transaction priority for concurrent transactions, perform a deposit and a withdrawal at the same time, and set a higher priority to deposit transactions. To simulate this, perform the two operations concurrently - a withdrawal in one session and a deposit in another session. The deposit transaction starts after the withdrawal has been initiated, but occurs before the withdrawal is completed from a separate session, as demonstrated in the following table:

set yb_transaction_priority_lower_bound = 0.4;
set yb_transaction_priority_upper_bound= 0.6;
    

set yb_transaction_priority_lower_bound = 0.7;
set yb_transaction_priority_upper_bound= 0.9;
    

begin transaction /*lower priority transaction*/;
update account set balance = balance - 100::money
    where name='kevin' and type='checking';
    

select * from account;
 name  |   type   | balance
-------+----------+---------
 kevin | checking | $400.00
 kevin | saving   | $500.00
(2 rows)
    

begin transaction /*high priority transaction*/;
update account set balance = balance + 200::money
    where name='kevin' and type='checking';
    

select * from account;
 name  |   type   | balance
-------+----------+---------
 kevin | checking | $700.00
 kevin | saving   | $500.00
(2 rows)
    

select * from account;
ERROR:  Operation failed. Try again: Unknown transaction,
        could be recently aborted: XXXX
    

commit;
COMMIT
yugabyte=> select * from account;
 name  |   type   | balance
-------+----------+---------
 kevin | checking | $700.00
 kevin | saving   | $500.00
(2 rows)
    

Show transaction priority types

The yb_get_current_transaction_priority function shows the transaction priority of the current transaction and the priority bucket the given priority belongs in. Transaction priority buckets are explained in detail in Transaction Priorities. The following example demonstrates the usage of yb_get_current_transaction_priority.

1. From an active ysqlsh shell, create a table as follows:

   CREATE TABLE test_scan (i int, j int);
   

2. Start by setting the lower and upper bound values for your transaction.

   set yb_transaction_priority_lower_bound = 0.4;
   set yb_transaction_priority_upper_bound = 0.6;
   

3. In a transaction block, perform an insert and view the transaction priority as follows:

   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   INSERT INTO test_scan (i, j) values (1, 1), (2, 2), (3, 3);
   SELECT yb_get_current_transaction_priority();
   COMMIT;
   

       yb_get_current_transaction_priority
   -------------------------------------------
     0.537144608 (Normal priority transaction)
   (1 row)
   

4. In the next transaction block, perform a SELECT ... FOR UPDATE, which results in a high priority transaction.

   set yb_transaction_priority_lower_bound = 0.1;
   set yb_transaction_priority_lower_bound = 0.4;
   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT i, j FROM test_scan WHERE i = 1 FOR UPDATE;
   SELECT yb_get_current_transaction_priority();
   COMMIT;
   

       yb_get_current_transaction_priority
   -------------------------------------------
    0.212004009 (High priority transaction)
   (1 row)
   

   The transaction priority is randomly chosen between the lower and upper bound.

5. In the final transaction block, set yb_transaction_priority_upper_bound and yb_transaction_priority_lower_bound to be 1, and perform the same SELECT ... FOR UPDATE query as the previous one. This transaction type is of the highest priority.

   set yb_transaction_priority_upper_bound = 1;
   set yb_transaction_priority_lower_bound = 1;
   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT i, j FROM test_scan WHERE i = 1 FOR UPDATE;
   SELECT yb_get_current_transaction_priority();
   COMMIT;
   

   yb_get_current_transaction_priority
   -------------------------------------
   Highest priority transaction
   (1 row)
   

For more information, see Transaction priorities.