在之前的理解中SELECT语句只会在对象上获取共享锁，在行上面由于MVCC机制不会申请任何锁，所以SELECT语句不会被阻塞。在PG环境中SQL语句申请的对象级别的锁类型是relation，SELECT语句仍然需要在表上面申请共享访问级别的relation锁，在遇到访问排它锁争用的情况，就会出现SELECT语句一样被阻塞，所以此时就出现了SELECT被阻塞的现象。这种现象可以非常容易的模拟出来。

模拟行级排它锁

这里通过更新同一行就可以模拟行级排它锁。

会话一： postgres_1721@postgres > update big_table set id=12313123121212312 where id=1; UPDATE 1 Time: 0.788 ms 会话二： postgres_1721@postgres > update big_table set data2='1473c5de4ec1d74cce391fd78b1601fb' where id=1; 

查看锁的信息：

postgres_1721@postgres > \i block.sql -[ RECORD 1 ]-+-------------------------------------------------------------------------- pid | 2399 usename | postgres datname | postgres state | idle in transaction wait_event | Client: ClientRead time_state_s | 2111 time_xact_s | 2111 locked_object | locktype | mode | h_p_t | w_p_t | blocking_pids | last_session | lock_depth | 2399.0 query | RELEASE pg_psql_temporary_savepoint -[ RECORD 2 ]-+-------------------------------------------------------------------------- pid | 2760 usename | postgres datname | postgres state | active wait_event | Lock: transactionid time_state_s | 2250 time_xact_s | 2250 locked_object | transactionid locktype | transactionid mode | ShareLock h_p_t | w_p_t | big_table:0:1 blocking_pids | {2399} last_session | 2399 lock_depth | 2399.1 query | update big_table set data2='1473c5de4ec1d74cce391fd78b1601fb' where id=1; 

这里可以看到2760会话因为transactionid锁而被阻塞。

模拟访问排它锁

在上面的情况下执行表的DDL语句即可以模拟出访问排它锁

会话三： postgres_1721@postgres > alter table big_table add column data4 text; hang住了 

查看阻塞的信息：

postgres_1721@postgres > \i block.sql -[ RECORD 1 ]-+-------------------------------------------------------------------------- pid | 2399 usename | postgres datname | postgres state | idle in transaction wait_event | Client: ClientRead time_state_s | 757 time_xact_s | 757 locked_object | locktype | mode | h_p_t | w_p_t | blocking_pids | last_session | lock_depth | 2399.0 query | RELEASE pg_psql_temporary_savepoint -[ RECORD 2 ]-+-------------------------------------------------------------------------- pid | 2760 usename | postgres datname | postgres state | active wait_event | Lock: transactionid time_state_s | 617 time_xact_s | 617 locked_object | transactionid locktype | transactionid mode | ShareLock h_p_t | w_p_t | big_table:0:1 blocking_pids | {2399} last_session | 2399 lock_depth | 2399.1 query | update big_table set data2='1473c5de4ec1d74cce391fd78b1601fb' where id=1; -[ RECORD 3 ]-+-------------------------------------------------------------------------- pid | 2592 usename | postgres datname | postgres state | active wait_event | Lock: relation time_state_s | 13 time_xact_s | 2867 locked_object | big_table locktype | relation mode | AccessExclusiveLock h_p_t | w_p_t | blocking_pids | {2760,2399} last_session | 2399 lock_depth | 2760.3 query | alter table big_table add column data4 text; 

这里alter的语句申请对象的big_table的访问排它锁而被阻塞了。

SELECT语句被阻塞

任一执行一条SQL语句：

会话四： postgres_1721@postgres > \i mypid.sql pg_backend_pid ---------------- 2861 (1 row) Time: 0.612 ms postgres_1721@postgres > select * from big_table limit 1; hang住了。 

查询阻塞的信息：

-[ RECORD 3 ]-+-------------------------------------------------------------------------- pid | 2861 usename | postgres datname | postgres state | active wait_event | Lock: relation time_state_s | 111 time_xact_s | 126 locked_object | big_table locktype | relation mode | AccessShareLock h_p_t | w_p_t | blocking_pids | {2592} last_session | 2592 lock_depth | 2592.2 query | select * from big_table limit 1; -[ RECORD 4 ]-+-------------------------------------------------------------------------- 

相比之前的记录会多出上面的信息，select语句申请big_table访问共享锁时被阻塞了。