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.
Flag | Valid Range | Description |
---|---|---|
yb_transaction_priority_lower_bound |
Any value between 0 and 1, lower than the upper bound | Minimum transaction priority for transactions run in this session |
yb_transaction_priority_upper_bound |
Any value between 0 and 1, higher than the lower bound | Maximum transaction priority for transactions run in this session |
To view the transaction priority of the active transaction in current session, use the yb_get_current_transaction_priority
function.
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.
Examples
Create a YugabyteDB universe and open two separate ysqlsh connections to it.
Tip - Use YugabyteDB Managed
You can create a free cluster with YugabyteDB Managed, 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.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:
session #1 (withdrawal, low priority) | session #2 (deposit, high priority) |
Set the transaction priority to a lower range.
|
Set the transaction priority to a higher range.
|
Initiate the withdrawal of $100.
The transaction has started, though not committed yet.
|
|
Next, initiate the deposit of $200, which should have higher priority.
The transaction has started, though not committed yet.
|
|
The withdrawal transaction will now abort because it conflicts with the higher priority deposit transaction.
|
|
The deposit transaction can now commit.
|
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
.
-
From an active ysqlsh shell, create a table as follows:
CREATE TABLE test_scan (i int, j int);
-
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;
-
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)
-
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.
-
In the final transaction block, set
yb_transaction_priority_upper_bound
andyb_transaction_priority_lower_bound
to be 1, and perform the sameSELECT ... 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.