本文简单介绍了PostgreSQL Locks(locktype：transactionid)的基础知识.
PostgreSQL系统表pg_locks中的locktype,有一种称为transactionid的锁类型,PG文档的说法是:
Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
In Postgres, every transaction takes an exclusive lock on its own transactionid when it starts. Sometimes, when a transaction wants to wait for another transaction to complete, it’ll try to take share lock on that other transaction’s id.This will of course block until the exclusive lock goes away.
Currently, the only case where anything will try to take a sharelock on transaction id is when it is blocking on a row-level lock as a result of trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
other transaction already modified or deleted or selected FOR UPDATE/SHARE. (Why this doesn’t show up as a more obvious row-level lock in pg_locks is an interesting technical detail, but you probably don’t care that much about that.)Given what you’re showing in pg_stat_activity, the most likely bet is that the “idle in transaction” client is sitting on an uncommitted row modification. You need to whack it upside the head and convince it to commit or abort its modifications a bit more promptly. The dependency could be a bit indirect —- for instance, modifying a row that is linked by a foreign key dependency to the one the second transaction wants to change —- but it’s a very general rule that sitting on uncommitted modifications for any length of time is Bad Behavior.
如下例所示,session 1执行update语句,session 2 update相同的rows,session 3查询locktype为transactionid的信息.
session 1
[local]:5432 pg12@testdb=# begin;
ere relation=295053;
BEGIN
Time: 1.430 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* select pg_backend_pid();
pg_backend_pid
----------------
2475
(1 row)
Time: 2.619 ms
[local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3;
UPDATE 4
Time: 7.892 ms
[local]:5432 pg12@testdb=#* select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation=295053;
-[ RECORD 1 ]------+-----------------
pid | 2475
locktype | relation
relation | t_lock
mode | RowExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/2
granted | t
fastpath | t
Time: 9.013 ms
session 2
[local]:5432 pg12@testdb=# ---- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.117 ms
[local]:5432 pg12@testdb=#* select pg_backend_pid();
pg_backend_pid
----------------
2480
(1 row)
Time: 1.825 ms
[local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3;
-- 阻塞/挂起
session 3
[local]:5432 pg12@testdb=# select * from pg_locks where pid <> pg_backend_pid() and locktype = 'transactionid';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+----------
transactionid | | | | | | 669310 | | | | 3/2 | 2475 | ExclusiveLock | t | f
transactionid | | | | | | 669312 | | | | 4/4 | 2480 | ExclusiveLock | t | f
transactionid | | | | | | 669310 | | | | 4/4 | 2480 | ShareLock | f | f
(3 rows)
Time: 1.243 ms
可以看到,进程2475中的事务669310和进程2480中的669312分别持有transactionid的ExclusiveLock,进程2480在等待事务ID=669310的lock(granted=f).
为什么会等待669310的ShareLock呢?回过头来查看t_lock表的xmax信息:
[local]:5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3;
xmin | xmax | ctid
--------+--------+---------
669246 | 669310 | (0,3)
669247 | 669310 | (4,99)
669248 | 669310 | (8,195)
669252 | 669310 | (13,65)
(4 rows)
Time: 4.715 ms
可以看到 : 待更新的tuple.xmax = 669310.
回滚事务669310,再次查看xmax:
[local]:5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3;
xmin | xmax | ctid
--------+--------+---------
669246 | 669312 | (0,3)
669247 | 669312 | (4,99)
669248 | 669312 | (8,195)
669252 | 669312 | (13,65)
(4 rows)
Time: 1.182 ms
[local]:5432 pg12@testdb=# SELECT pid,backend_xid,wait_event_type,wait_event,state,query FROM pg_stat_activity WHERE pid IN (2475,2480);
-[ RECORD 1 ]---+------------------------------------------
pid | 2475
backend_xid |
wait_event_type | Client
wait_event | ClientRead
state | idle
query | rollback;
-[ RECORD 2 ]---+------------------------------------------
pid | 2480
backend_xid | 669312
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
query | update t_lock set id = 3000 where id = 3;
Time: 5.434 ms
xmax被更新为669312.