Use case - daily limit: SELECT FOR UPDATE vs. UPDATE RETURNING in 🐘Aurora and 🚀YugabyteDB
Tags: aws, aurora, yugabytedb, sql
Published: 2022-09-28 15:09:17

Here is a simple use case: payement operations with a daily limit. A customer can execute multiple operations at the same time, and the limit cannot be lower than zero. You need to be sure that when you read the current limit, no other operation can happen until you commit yours.

**TL;DR**: there are two ways to speed it up: use optimal SQL or run on YugabyteDB 🚀

I will run this on Amazon Aurora with PostgreSQL compatibility, as an example of the best High Availability for a monolithic database (storage is multi-AZ), and on YugabyteDB which is distributed, scaling out reads and writes with the highest Availability, also in a multi-AZ deployement.

I create the following table to store a daily limit per customer per day:
```sql
drop table if exists daily_limit;
create table daily_limit (
 customer_id bigint,
 day date,
 daily_limit bigint check (daily_limit>0),
 primary key (customer_id, day),
 last_update timestamp default now()
) --split into 10 tablets
;
```
I insert 4 customers and 1000 days (but will query only one day):
```sql
insert into daily_limit (customer_id, day, daily_limit)
select customer_id, day, 1e9 from
( select date '2022-01-01' + generate_series(1,1000) as day) days,
(select generate_series(1,4)  as customer_id) customers
;
```
My pgbench custom script is subtracting a random amount on a random customer, and reads the limit to check it (but anyway, the check constraint takes care of it):
```sql
\set customer random(1,4)
\set amount   random(1,10)
begin transaction;
select daily_limit from daily_limit 
 where customer_id=:customer and day=now()::date for update
\gset
\set daily_limit :daily_limit - :amount
update daily_limit 
 set daily_limit=:daily_limit, last_update=now() 
 where customer_id=:customer and day=now()::date;
commit;
```
I run it with `pgbench`:
```sh
pgbench -nf /tmp/daily_limit_pgbench.sql -M extended -T 900 -c 100
```

This runs 100 threads on 4 customers, to stress the hotspot scenario. The RAM and I/O doesn't really matter for this as we will see by looking at the resource usage.

## Aurora db.r6g.2xlarge

I'm running the pgbench above on an Aurora `db.r6g.2xlarge` (8 vCPU - Graviton 2) instance. First with the exact script above

### SELECT ... FOR UPDATE

During the execution, I can see that the bottleneck is waiting on row lock `Tuple:lock` for the `select for update`, which is not surprising at all. Once the row lock is acquired, the update is fast.

![Transactions per second](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/84acal47htjt502kdj6k.png)

![Average Active Sessions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g8pq13y0iugjj4rozfqz.png)

![SQL Statements](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y5mu98pu7820oz72yhes.png)

This is not surprising as it is not scalable by design, with 100 sessions reading the same 4 rows.

Here is the `pgbench` result:

```sql
pgbench (15devel, server 13.7)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 1
duration: 900 s
number of transactions actually processed: 54401
latency average = 1623.978 ms
initial connection time = 20440.648 ms
tps = 61.577197 (without initial connection time)
```

This is 60 transactions per seconds at more than 1 second average latency. Now, let's do better.

### UPDATE ... RETURNING

Instead of reading the value, and updating it, I update it and read the value:

```sql
\set customer random(1,4)
\set amount   random(1,10)
begin transaction;
update daily_limit set daily_limit=daily_limit-:amount, last_update=now() where customer_id=:customer and day=now()::date returning daily_limit-:amount as daily_limit
\gset
commit;
```

This is functionally equivallent. The `\gset` gets the same value for `:daily_limit` here as it did before.

Now, instead of waiting to acquire the lock on the row (`lock:tuple` in dark red), we wait on the other transaction (`lock:transactionid` in light red), but with a higher thoughput:

![AAS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n6ixv1e1xy25uubfj7si.png)

![SQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xqtk1t9jj3sbf4mg5h6i.png)

The `pgbench` result is:

```sql
pgbench (15devel, server 13.7)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 1
duration: 900 s
number of transactions actually processed: 103483
latency average = 851.452 ms
initial connection time = 20581.529 ms
tps = 117.446446 (without initial connection time)
```

The thoughput has increased by two here, and the latency decreased by two, which is a clear benefit.

### SELECT FOR SHARE

I tried a `select ... for share` to see if the first solution was better without taking immediately the exclusive lock. But it failed with deadlock:
```sql
pgbench (15devel, server 13.7)
pgbench: error: client 43 script 0 aborted in command 5 query 0: ERROR:  deadlock detected
DETAIL:  Process 4208 waits for ShareLock on transaction 181709; blocked by process 4173.
Process 4173 waits for ShareLock on transaction 181707; blocked by process 4208.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (7,128) in relation "daily_limit"
```

I didn't look into the details. If you think that can avoid deadlocks by ordering the DML statements in the transaction, then you have a proof that it is not sufficient as all transactions are the same here.

## Yugabyte 3x c5.4xlarge

Let's now run on YugabyteDB, PostgreSQL compatible distributed SQL database.

![YugabyteDB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/we6xm4iu2o1qzm4qce11.png)

### SELECT ... FOR UPDATE

I've done the same as above on a YugabyteDB cluster. I have 3 nodes here with `c5.4xlarge` just because this is my default cluster, but this doesn't really matter here. The resource consumption is very low. I wanted to be sure that the throughput can scale and I've split the table to 10 tablets to be sure that my 4 rows are on different nodes.

YugabyteDB, in this version, uses optimistic locking (with exponential retries rather than wait list) but offers the same Read Committed semantic as PostgreSQL because I've set `yb_enable_read_committed_isolation`:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jyy3iegnycokkagz7va1.png)

I'm running in version YugabyteDB `2.15.1`

### SELECT ... FOR UPDATE

```sql
pgbench (15devel, server 11.2-YB-2.15.1.0-b0)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 1
duration: 900 s
number of transactions actually processed: 115433
latency average = 777.159 ms
initial connection time = 6679.005 ms
tps = 128.673725 (without initial connection time)
```
This, even with `select ... for update` is better than the best Aurora result above with the optimized code.

### UPDATE ... RETURNING

Now running the optimized code.
```sql
pgbench (15devel, server 11.2-YB-2.15.1.0-b0)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 1
duration: 900 s
number of transactions actually processed: 191716
latency average = 467.408 ms
initial connection time = 6644.640 ms
tps = 213.945698 (without initial connection time)
```
With the `update ... returning` I have more than 200 transactions per seconds with a 500 millisecond latency.


Looking at the resource usage, it is obvious that the number of CPU, RAM or I/O doesn't matter. This is all about the lock management:
![YBAnyware](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hllcxy95jb1stnkjbux2.png)

## Other observations and remarks

- My Aurora instance above was with Graviton 2 `db.r6g.2xlarge` (Arm processor). By curiosity I've run the same with a `db.r6i.2xlarge` Intel and got the same (60 tx/s at 1700ms latency for the first test, and 115tx/s at 800ms for the second one). Graviton 2, for this, is cheaper but not faster.

- `pgbench` runs on PostgreSQL compatible databases. It would be interesting to do the same on Oracle Database, with and without Data Guard protection, and with and without RAC load balancing. When it comes to hotspots, locks and transactions, you cannot compare databases with different level of protection and scalability. To get a level of HA comparable to YugabyteDB you need RAC and Data Guard in maximum protection mode. This adds latency on the current block shipping, the distributed lock and on transaction commits.

- In `pgbench` if an error occurs, the client ends. When expecting serialization errors, you can use the Yugabyte version of it `ysql_bench` which handles retries.

- With YugabyteDB optimistic locking, there are parameters to control the optimistic locking retries. This can be set in the transaction:
```sql
set local retry_backoff_multiplier=2;
set local retry_max_backoff='1s'; 
set local retry_min_backoff='100ms';
```
Those values are the default ones (choose a retry delay between 100 and 1000 millisecond, and multiply by two for each retry). You can get higher throughput by tuning that, but this depends on your application behavior.

For the test above with `update ... returning` I get the following:
```sql
pgbench (15devel, server 11.2-YB-2.15.1.0-b0)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 1
duration: 60 s
number of transactions actually processed: 14542
latency average = 372.433 ms
initial connection time = 6655.605 ms
tps = 268.504754 (without initial connection time)
```
with this:
```sql
\set customer random(1,4)
\set amount   random(1,10)
begin transaction;
set local retry_backoff_multiplier=2;   -- default 2
set local retry_max_backoff='100ms';    -- default 1s
set local retry_min_backoff='10ms';     -- default 100ms
update daily_limit set daily_limit=daily_limit-:amount, last_update=now() where customer_id=:customer and day=now()::date returning daily_limit-:amount as daily_limit
\gset
commit;
```

This is more than 250 transactions per second with less than 400 milliseconds latency from 100 threads updating the same 4 rows.

- You can do better with a stored procedure. If I create:
```sql
create or replace procedure run(c bigint, a int) as $$
 update daily_limit 
 set daily_limit=daily_limit-a, last_update=now() 
 where customer_id=c and day=now()::date 
 returning daily_limit-a as daily_limit
$$ language sql;
```
the script is reduced to:
```sql
\set customer random(1,4)
\set amount   random(1,10)
call run(:customer,:amount);
```
and I run it with the simple protocol:
```sh
pgbench -nf /tmp/daily_limit_pgbench.sql -T 900 -c 100
```
and I get:
![YSQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vciciz6nbj06v2a4qyhs.png)
```
pgbench (15devel, server 11.2-YB-2.15.3.0-b0)
transaction type: /tmp/daily_limit_pgbench.sql
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 900 s
number of transactions actually processed: 486787
latency average = 183.903 ms
initial connection time = 6234.570 ms
tps = 543.766181 (without initial connection time)
```
500 transaction per second with less than 200 milliseconds latency.

- Final note: this is about speedup, not scalability. This application is not scalable by design. There are ways to improve like I did for the default `pgbench` workload in a previous post [Avoiding hotspots in pgbench on 🐘or🚀](https://dev.to/yugabyte/avoiding-hotspots-in-pgbench-on-or-38lk). But measuring that the speed of lock acquisition is similar, and even better, in a distributed architecture like YugabyteDB is a great result.