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.
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.