| session #1 (withdrawal, low priority) |
session #2 (deposit, high priority) |
Set the transaction priority to a lower range.
set yb_transaction_priority_lower_bound = 0.4;
set yb_transaction_priority_upper_bound= 0.6;
|
Set the transaction priority to a higher range.
set yb_transaction_priority_lower_bound = 0.7;
set yb_transaction_priority_upper_bound= 0.9;
|
Initiate the withdrawal of $100.
begin transaction /*lower priority transaction*/;
update account set balance = balance - 100::money
where name='kevin' and type='checking';
The transaction has started, though not committed yet.
select * from account;
name | type | balance
-------+----------+---------
kevin | checking | $400.00
kevin | saving | $500.00
(2 rows)
|
|
|
|
Next, initiate the deposit of $200, which should have higher priority.
begin transaction /*high priority transaction*/;
update account set balance = balance + 200::money
where name='kevin' and type='checking';
The transaction has started, though not committed yet.
select * from account;
name | type | balance
-------+----------+---------
kevin | checking | $700.00
kevin | saving | $500.00
(2 rows)
|
The withdrawal transaction will now abort because it conflicts with the higher priority deposit transaction.
select * from account;
ERROR: Operation failed. Try again: Unknown transaction,
could be recently aborted: XXXX
|
|
|
|
The deposit transaction can now commit.
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](../../../architecture/transactions/transaction-priorities/). The following example demonstrates the usage of `yb_get_current_transaction_priority`.
1. From an active [ysqlsh](../../../api/ysqlsh/#starting-ysqlsh) shell, create a table as follows:
```sql
CREATE TABLE test_scan (i int, j int);
```
1. Start by setting the lower and upper bound values for your transaction.
```sql
set yb_transaction_priority_lower_bound = 0.4;
set yb_transaction_priority_upper_bound = 0.6;
```
1. In a transaction block, perform an insert and view the transaction priority as follows:
```sql
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;
```
```output
yb_get_current_transaction_priority
-------------------------------------------
0.537144608 (Normal priority transaction)
(1 row)
```
1. In the next transaction block, perform a `SELECT ... FOR UPDATE`, which results in a high priority transaction.
```sql
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;
```
```output
yb_get_current_transaction_priority
-------------------------------------------
0.212004009 (High priority transaction)
(1 row)
```
The transaction priority is randomly chosen between the lower and upper bound.
1. 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.
```sql
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;
```
```output
yb_get_current_transaction_priority
-------------------------------------
Highest priority transaction
(1 row)
```
For more information, see [Transaction priorities](../../../architecture/transactions/transaction-priorities/).