0

I would like to test deadlocks on PostgreSQL 13 using pgAdmin 4, with different lock types and isolation levels.

So far, I have tried opening two pgAdmin tabs and running different transaction blocks like these:

--LOCK stats IN SHARE ROW EXCLUSIVE MODE;
--LOCK stats IN ROW SHARE MODE;
--LOCK stats IN ROW EXCLUSIVE MODE;
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE stats SET clicks = 10 WHERE id = 59;
UPDATE stats SET leads = 10 WHERE id = 60;
UPDATE stats SET calls = 10 WHERE id = 59;
UPDATE stats SET reviews = 10 WHERE id = 60;
UPDATE stats SET saves = 10 WHERE id = 59;
UPDATE stats SET bookings = 10 WHERE id = 60;
COMMIT;

--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE stats SET clicks = 10 WHERE vendor_id = 60;
UPDATE stats SET leads = 10 WHERE vendor_id = 59;
UPDATE stats SET calls = 10 WHERE vendor_id = 60;
UPDATE stats SET reviews = 10 WHERE vendor_id = 59;
UPDATE stats SET saves = 10 WHERE vendor_id = 60;
UPDATE stats SET bookings = 10 WHERE vendor_id = 59;
COMMIT;

But to my surprise, rows are updated perfectly fine regardless of the lock type and isolation level. Reading the documentation I assume the default table lock is ROW EXCLUSIVE and the default transaction isolation level is READ COMMITTED.

I guess both transaction blocks are never executed concurrently when running on different pgAdmin tabs. Is this the expected behavior or am I doing something wrong? How could I run both transaction blocks in different threads?

Thanks in advance.

JourneyToJsDude
  • 187
  • 1
  • 3
  • 13
  • 2
    Try interspersing pg_sleep(1) between each update statement. – Belayer May 23 '22 at 02:16
  • it just only works for me inside a function with `PERFORM pg_sleep(1);`, how do you call it inside a simple sql transaction block? thanks. – JourneyToJsDude May 23 '22 at 10:22
  • in any case, i would not expect any deadlock because you are pausing the execution of each update transaction just after it has finished and unlocked the row... when executing the function with `pg_sleep` all rows are updated. – JourneyToJsDude May 23 '22 at 10:28

1 Answers1

0

Anyway, to avoid deadlocks I have implemented the different transactions (insert, update) inside a procedure and released the locks with COMMIT after each transaction.

For testing it, I have deployed with docker-compose different services that call the procedure. This procedure updates the same table rows and is called repeatedly.

JourneyToJsDude
  • 187
  • 1
  • 3
  • 13