在之前的理解中SELECT語句只會在對象上獲取共享鎖，在行上面由于MVCC機制不會申請任何鎖，所以SELECT語句不會被阻塞。在PG環(huán)境中SQL語句申請的對象級別的鎖類型是relation，SELECT語句仍然需要在表上面申請共享訪問級別的relation鎖，在遇到訪問排它鎖爭用的情況，就會出現(xiàn)SELECT語句一樣被阻塞，所以此時就出現(xiàn)了SELECT被阻塞的現(xiàn)象。這種現(xiàn)象可以非常容易的模擬出來。
模擬行級排它鎖
這里通過更新同一行就可以模擬行級排它鎖。
會話一：
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鎖而被阻塞。
模擬訪問排它鎖
在上面的情況下執(zhí)行表的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語句被阻塞
任一執(zhí)行一條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訪問共享鎖時被阻塞了。
總結(jié)
通過上面的列子中其實可以看到SELECT語句一樣的會被阻塞，不過在PG環(huán)境中SELECT的阻塞分析起來比Oracle還要更簡單一些，因為在Oracle中元數(shù)據(jù)的鎖不會出現(xiàn)在v$lock中而是出現(xiàn)在dba_kgllock中，同時event也是顯示的元數(shù)據(jù)等待事件，相比PG來說這點會更直觀很多。
轉(zhuǎn)自https://www.cnblogs.com/www-htz-pw/p/19016168
該文章在 2025/10/14 8:54:10 編輯過
400 186 1886
