如何使用PGADMIN测试POSGRESQL僵局
我想使用PGADMIN 4在Postgresql 13上测试死锁，具有不同的锁定类型和隔离水平。
到目前为止，我已经尝试打开两个PGADMIN选项卡并运行类似的交易块：
--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;
但是令我惊讶的是，无论锁定类型和隔离水平如何，行都可以很好地更新。读取文档，我假设默认表锁定为ROW独家，而默认事务隔离级别为读取订单。
我想在不同的PGADMIN选项卡上运行时，这两个交易块从未同时执行。这是预期的行为还是我做错了什么？如何在不同线程中运行两个交易块？
提前致谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
无论如何，为了避免僵局，我已经在一个过程中实现了不同的交易（插入，更新），并在每次交易后用
commit释放锁。为了测试它，我已经使用
Docker-Compose不同的服务来调用该过程。此过程更新同一表行，并反复调用。Anyway, to avoid deadlocks I have implemented the different transactions (insert, update) inside a procedure and released the locks with
COMMITafter each transaction.For testing it, I have deployed with
docker-composedifferent services that call the procedure. This procedure updates the same table rows and is called repeatedly.