PostgreSQL由于多版本的实现,实际读取行数据时,并不会在行上执行任何锁(包括读锁)
A. 正确
B. 错误
参考答案:A
解析:
1、查看当前琐的状态
testdb=# select locktype,database,relation::regclass,transactionid,pid,mode from pg_locks;
locktype | database | relation | transactionid | pid | mode
------------+----------+----------+---------------+-------+-----------------
relation | 16391 | pg_locks | | 18405 | AccessShareLock
virtualxid | | | | 18405 | ExclusiveLock
(2 rows)
2、开启事务1437修改数据
testdb=# begin;
BEGIN
testdb=*# update t1 set id=111 where id=11;
UPDATE 1
testdb=*# select pg_backend_pid();
pg_backend_pid
----------------
18973
(1 row)
testdb=*# select txid_current();
txid_current
--------------
1437
(1 row)
3、开启事务1438查询数据
testdb=# begin;
BEGIN
testdb=*# select * from t1;
id
----
11
(1 row)
testdb=*# select pg_backend_pid();
pg_backend_pid
----------------
18505
(1 row)
testdb=*# select txid_current();
txid_current
--------------
1438
(1 row)
4、查看锁
testdb=# select locktype,database,relation::regclass,transactionid,pid,mode from pg_locks;
locktype | database | relation | transactionid | pid | mode
---------------+----------+----------+---------------+-------+------------------
relation | 16391 | pg_locks | | 18405 | AccessShareLock
virtualxid | | | | 18405 | ExclusiveLock
relation | 16391 | t1 | | 18973 | RowExclusiveLock
virtualxid | | | | 18973 | ExclusiveLock
relation | 16391 | t1 | | 18505 | AccessShareLock
virtualxid | | | | 18505 | ExclusiveLock
transactionid | | | 1437 | 18973 | ExclusiveLock
(7 rows)
5、继续在事务1438执行update
testdb=*# update t1 set id=111 where id=11;
。。。。hang死
6、查看琐
testdb=# select locktype,database,relation::regclass,transactionid,pid,mode from pg_locks;
locktype | database | relation | transactionid | pid | mode
---------------+----------+----------+---------------+-------+------------------
relation | 16391 | pg_locks | | 18405 | AccessShareLock
virtualxid | | | | 18405 | ExclusiveLock
relation | 16391 | t1 | | 18973 | RowExclusiveLock
virtualxid | | | | 18973 | ExclusiveLock
relation | 16391 | t1 | | 18505 | AccessShareLock
virtualxid | | | | 18505 | ExclusiveLock
transactionid | | | 1437 | 18973 | ExclusiveLock
(7 rows)
结论:
查询语句对应的后台进程18505,在对象表t1上申请了AccessShareLock 作者:云贝教育 https://www.bilibili.com/read/cv28379015/ 出处:bilibili