Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to master the basic knowledge of PostgreSQL Locks". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to master the basic knowledge of PostgreSQL Locks".

As shown in the following example, session 1 executes the update statement, and session 2 update the same rows,session 3 queries for information that locktype is transactionid.

Session 1

[local]: 5432 pg12@testdb=# begin;ere relation=295053;BEGINTime: 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 4Time: 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 | 2475locktype | relationrelation | t_lockmode | RowExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/2granted | tfastpath | tTime: 9.013 ms

Session 2

[local]: 5432 pg12@testdb=#-session 2 [local]: 5432 pg12@testdb=# begin;BEGINTime: 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 Ten-Muir-blocked / suspended

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 | classid | objsubid | virtualtransaction | pid | mode | granted | fastpath-+-- -+-transactionid | | 669310 | | | 3xer2 | 2475 | ExclusiveLock | t | f transactionid | | 669312 | 4Univer 4 | 2480 | ExclusiveLock | t | f transactionid | | | | 669310 | 4 rows 4 | 2480 | ShareLock | f | f (3 ms) Time: 1.243 ms |

You can see that transaction 669310 in process 2475 and 669312 in process 2480 hold the ExclusiveLock of transactionid, respectively, and process 2480 is waiting for the lock (granted=f) of transaction ID=669310.

Why wait for the 669310 ShareLock? Go back to the xmax information of the t_lock table:

[local]: 5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3; xmin | xmax | ctid-+-+-669246 | 669310 | (0Power3) 669247 | 669310 | (4Power99) 669248 | 669310 | (8195) 669252 | 669310 | (13Magne 65) (4 rows) Time: 4.715 ms

You can see that the tuple.xmax to be updated is 669310.

Roll back transaction 669310 and view xmax again:

[local]: 5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3 Xmin | xmax | ctid-+-+-669246 | 669312 | (0Magne3) 669247 | 669312 | (4p99) 669248 | 669312 | (8195) 669252 | 669312 | (135.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 -[RECORD 1]-+-- pid | 2475backend_xid | wait_event_type | Clientwait_event | ClientReadstate | idlequery | rollback -[RECORD 2]-+-- pid | 2480backend_xid | 669312wait_event_type | Clientwait_event | ClientReadstate | idle in transactionquery | update t_lock set id = 3000 where id = 3 + time: 5.434 ms

Xmax is updated to 669312.

Thank you for reading, the above is the content of "how to master the basic knowledge of PostgreSQL Locks". After the study of this article, I believe you have a deeper understanding of how to master the basic knowledge of PostgreSQL Locks, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.