Transaction priorities

• YSQL

When using the Fail-on-Conflict concurrency control policy, transactions are assigned priorities that help decide which transactions should be aborted in case of conflict.

There are two priority buckets, each having a priority range of reals in [0, 1] as follows:

1. High-priority bucket: if the first statement in a transaction takes a FOR UPDATE/ FOR SHARE/ FOR NO KEY UPDATE explicit row lock using SELECT, it will be assigned a priority from this bucket.

2. Normal-priority bucket: all other transactions are assigned a priority from this bucket.

Note that a transaction with any priority P1 from the high-priority bucket can abort a transaction with any priority P2 from the normal-priority bucket. For example, a transaction with priority 0.1 from the high-priority bucket can abort a transaction with priority 0.9 from the normal-priority bucket.

Priorities are randomly chosen from the applicable bucket. However, you can use the following two YSQL parameters to control the priority assigned to transactions in a specific session:

• yb_transaction_priority_lower_bound
• yb_transaction_priority_upper_bound

These parameters help set lower and upper bounds on the randomly-assigned priority that a transaction should receive from the applicable bucket. These parameters accept a value of real datatype in the range [0, 1]. Also note that the same bounds apply to both buckets.

The yb_get_current_transaction_priority function can be used to fetch the transaction priority of the current active transaction. It outputs a pair <priority> (bucket), where <priority> is of a real datatype between [0, 1] with 9 decimal units of precision, and <bucket> is either Normal or High.

A transaction's priority is 0.000000000 (normal-priority transaction) until a transaction is really started.

Examples

The following examples demonstrate how to set priorities for your transactions and get the current transaction priority.

1. Create a table and insert some data.

   CREATE TABLE test (k INT PRIMARY KEY, v INT);
   INSERT INTO test VALUES (1, 1);
   

2. Set the lower and upper bound values for your transactions as follows:

   SET yb_transaction_priority_lower_bound = 0.4;
   SET yb_transaction_priority_upper_bound = 0.6;
   

3. Create a transaction in the normal-priority bucket as follows:

   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT yb_get_current_transaction_priority(); -- 0 due to an optimization which doesn't really start a real transaction internally unless a write occurs
   

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

   SELECT * FROM test;
   

    k | v
   ---+---
    1 | 1
   (1 row)
   

   SELECT yb_get_current_transaction_priority(); -- still 0 due to the optimization which doesn't really start a real transaction internally unless a write occurs
   

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

   INSERT INTO test VALUES (2, '2'); -- perform a write which starts a real     transaction
   SELECT yb_get_current_transaction_priority(); -- non-zero now
   

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

   COMMIT;
   

4. Create a transaction in the high-priority bucket as follows:

   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT * FROM test WHERE k = 1 FOR UPDATE; -- starts a transaction in a high-priority bucket
   

    k | v
   ---+---
    1 | 1
   (1 row)
   

   SELECT yb_get_current_transaction_priority();
   

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

   COMMIT;
   

5. Create a transaction with the highest priority

   SET yb_transaction_priority_upper_bound = 1;
   SET yb_transaction_priority_lower_bound = 1;
   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT * FROM test WHERE k = 1 FOR UPDATE;
   

    k | v
   ---+---
    1 | 1
   (1 row)
   

   SELECT yb_get_current_transaction_priority();
   

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

   COMMIT;