云贝教育 |【PostgreSQL PGCE-091题目解析24】PostgreSQL由于多版本的实现，实际读取行数据时，并不会在行上执行任何锁（包括读锁）

考试科目：PGCE-E-091

考试题量：44 道单项选择题、29 道多项选择题（每题 2 分）

通过分数：60%

考试时间：60min

本文为云贝教育刘峰（微信：yunbee_DBA）原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

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