Transaction priorities

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.

{{< note title="Note" >}} Currently, transaction priorities work in the following scenarios:

• Works with YSQL only, not supported for YCQL.
• Only applies for transactions using Fail-on-Conflict concurrency control policy.
• Only conflict resolution is prioritized, not resource consumption as a part.

{{< /note >}}

Examples

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

{{< tip title="Tip - Use YugabyteDB Aeon" >}} You can create a free cluster with YugabyteDB Aeon, and open two cloud shell connections to it. These cloud shell connections open in two different browser tabs, which you can use to do the steps that follow.

{{< /tip >}}

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:

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:

   sqlCopy

   CREATE TABLE test_scan (i int, j int);
   

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

   sqlCopy

   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:

   sqlCopy

   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;
   

   outputCopy

       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.

   sqlCopy

   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;
   

   outputCopy

       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.

   sqlCopy

   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;
   

   outputCopy

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

For more information, see Transaction priorities.