这篇文章主要讲解了“怎么掌握PostgreSQL Locks的基础知识”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么掌握PostgreSQL Locks的基础知识”吧！

如下例所示,session 1执行update语句,session 2 update相同的rows,session 3查询locktype为transactionid的信息.
session 1

[local]:5432pg12@testdb=#begin;ererelation=295053;BEGINTime:1.430ms[local]:5432pg12@testdb=#*--SELECT*fromt_lockwhereid<10FORUPDATE;[local]:5432pg12@testdb=#*selectpg_backend_pid();pg_backend_pid----------------2475(1row)Time:2.619ms[local]:5432pg12@testdb=#*updatet_locksetid=3000whereid=3;UPDATE4Time:7.892ms[local]:5432pg12@testdb=#*selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation=295053;-[RECORD1]------+-----------------pid|2475locktype|relationrelation|t_lockmode|RowExclusiveLockpage|tuple|virtualxid|transactionid|virtualtransaction|3/2granted|tfastpath|tTime:9.013ms

session 2

[local]:5432pg12@testdb=#----session2[local]:5432pg12@testdb=#begin;BEGINTime:1.117ms[local]:5432pg12@testdb=#*selectpg_backend_pid();pg_backend_pid----------------2480(1row)Time:1.825ms[local]:5432pg12@testdb=#*updatet_locksetid=3000whereid=3;--阻塞/挂起

session 3

[local]:5432pg12@testdb=#select*frompg_lockswherepid<>pg_backend_pid()andlocktype='transactionid';locktype|database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid|mode|granted|fastpath---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+----------transactionid||||||669310||||3/2|2475|ExclusiveLock|t|ftransactionid||||||669312||||4/4|2480|ExclusiveLock|t|ftransactionid||||||669310||||4/4|2480|ShareLock|f|f(3rows)Time:1.243ms

可以看到,进程2475中的事务669310和进程2480中的669312分别持有transactionid的ExclusiveLock,进程2480在等待事务ID=669310的lock(granted=f).
为什么会等待669310的ShareLock呢?回过头来查看t_lock表的xmax信息:

[local]:5432pg12@testdb=#selectxmin,xmax,ctidfromt_lockwhereid=3;xmin|xmax|ctid--------+--------+---------669246|669310|(0,3)669247|669310|(4,99)669248|669310|(8,195)669252|669310|(13,65)(4rows)Time:4.715ms

可以看到 : 待更新的tuple.xmax = 669310.
回滚事务669310,再次查看xmax:

[local]:5432pg12@testdb=#selectxmin,xmax,ctidfromt_lockwhereid=3;xmin|xmax|ctid--------+--------+---------669246|669312|(0,3)669247|669312|(4,99)669248|669312|(8,195)669252|669312|(13,65)(4rows)Time:1.182ms[local]:5432pg12@testdb=#SELECTpid,backend_xid,wait_event_type,wait_event,state,queryFROMpg_stat_activityWHEREpidIN(2475,2480);-[RECORD1]---+------------------------------------------pid|2475backend_xid|wait_event_type|Clientwait_event|ClientReadstate|idlequery|rollback;-[RECORD2]---+------------------------------------------pid|2480backend_xid|669312wait_event_type|Clientwait_event|ClientReadstate|idleintransactionquery|updatet_locksetid=3000whereid=3;Time:5.434ms

xmax被更新为669312.

感谢各位的阅读，以上就是“怎么掌握PostgreSQL Locks的基础知识”的内容了，经过本文的学习后，相信大家对怎么掌握PostgreSQL Locks的基础知识这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！