在PostgreSQL中,有多种类型的锁用于控制对数据库对象的并发访问。以下是PostgreSQL中常见的锁类型:
一、表级锁(Table-level Locks):
在PostgreSQL中,主要存在以下几种表级锁:
1、ACCESS SHARE锁(共享锁):
- 形成:SELECT语句默认获取的锁。
- 影响:不阻塞其他事务的读写操作,但阻塞其他事务的ACCESS EXCLUSIVE锁。
- 排查定位:通过查询pg_locks系统视图,锁模式字段为AccessShareLock。
- 处置:通常无需特殊处理,语句执行完会自动释放。
t1=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------+-----------
relation | 24595 | 12073 | | | | | | | | 3/2468 | 62636 | AccessShareLock | t | t |
2、ROW SHARE锁(行共享锁):
- 形成:SELECT FOR UPDATE/FOR SHARE语句获取的锁。
- 影响:不阻塞其他事务的读操作,但阻塞其他事务的写操作和EXCLUSIVE锁。
- 排查定位:通过pg_locks视图,锁模式字段为RowShareLock。
- 处置:语句执行完或事务提交/回滚后释放。避免长事务持有该锁。
t1=# \set AUTOCOMMIT off
t1=# \set
AUTOCOMMIT = 'off'
--session
t1=# select * from t1 for update;
--session 2
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------+-----------
relation | 24595 | 24596 | | | | | | | | 5/36 | 64912 | RowShareLock | t | t |
3、ROW EXCLUSIVE锁(行排它锁):
- 形成:INSERT、UPDATE、DELETE、TRUNCATE等DML语句获取的锁。
- 影响:阻塞其他事务的写操作和SHARE锁,不阻塞读操作。
- 排查定位:通过pg_locks视图,锁模式字段为RowExclusiveLock。
- 处置:语句执行完或事务提交/回滚后释放。避免长事务持有排它锁。
--session 1
t1=# update t1 set name='bj' where id=1;
UPDATE 1
t1=*#
--session 2
t1=# update t1 set name='sh' where id=1;
--session 3
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------+-------------------------------
relation | 24595 | 24611 | | | | | | | | 4/1000 | 67656 | RowExclusiveLock | t | t |
virtualxid | | | | | 4/1000 | | | | | 4/1000 | 67656 | ExclusiveLock | t | t |
relation | 24595 | 24611 | | | | | | | | 5/88 | 67169 | RowExclusiveLock | t | t |
virtualxid | | | | | 5/88 | | | | | 5/88 | 67169 | ExclusiveLock | t | t |
relation | 5 | 12073 | | | | | | | | 3/2485 | 68225 | AccessShareLock | t | t |
virtualxid | | | | | 3/2485 | | | | | 3/2485 | 68225 | ExclusiveLock | t | t |
transactionid | | | | | | 781 | | | | 5/88 | 67169 | ExclusiveLock | t | f |
tuple | 24595 | 24611 | 0 | 1 | | | | | | 4/1000 | 67656 | ExclusiveLock | t | f |
transactionid | | | | | | 782 | | | | 4/1000 | 67656 | ExclusiveLock | t | f |
transactionid | | | | | | 781 | | | | 4/1000 | 67656 | ShareLock | f | f | 2024-03-15 16:57:47.249866+08
(10 rows)
postgres=# select datid,datid,pid,usesysid,client_addr,client_hostname,client_port,state_change,wait_event_type,wait_event,backend_xid,query from pg_stat_activity where backend_type='client backend';
24595 | 24595 | 67656 | 10 | | | -1 | 2024-03-15 16:57:47.24915+08 | Lock | transactionid | 782 | 781 | update t1 set name='sh' where id=1;
24595 | 24595 | 67169 | 10 | | | -1 | 2024-03-15 16:57:33.297348+08 | Client | ClientRead | 781 | | update t1 set name='bj' where id=1;
可以看到pid为67656的等待事件类型为Lock,等待事件为transactionid，backend_xid为782，backend_xmin为781。而67169的等待事件类型为Client,等待事件为ClientRead，backend_xid为781。
[root@db01 ~]# ps -ef|grep 67656
postgres 67656 54128 0 16:56 ? 00:00:00 postgres: postgres t1 [local] UPDATE waiting
[root@db01 ~]# ps -ef|grep 67169
postgres 67169 54128 0 16:55 ? 00:00:00 postgres: postgres t1 [local] idle in transaction
通过后台进程也能看到两个会话的状态，一个处于事务空闲，一个处于等待。那么可以得出查询阻塞的办法为查询pg_activety_stat或者直接查询后台进程
select datid,datid,pid,usesysid,client_addr,client_hostname,client_port,state_change,wait_event_type,wait_event,backend_xid,backend_xmin,query from pg_stat_activity where backend_type='client backend' and backend_xmin in (select backend_xid from pg_stat_activity);
select datid,datid,pid,usesysid,client_addr,client_hostname,client_port,state_change,wait_event_type,wait_event,backend_xid,backend_xmin,query from pg_stat_activity where backend_type='client backend' and backend_xmin is not null;
4、SHARE UPDATE EXCLUSIVE锁(共享排它锁):
1. 形成共享排他锁的情况：
- 当一个事务需要对表进行结构修改操作时，如ALTER TABLE语句，会请求共享排他锁。
- 当一个事务需要在表上创建索引时，也会请求共享排他锁。
- 当一个事务需要在表上执行VACUUM FULL或CLUSTER操作时，也会请求共享排他锁。
2. 共享排他锁的影响：
- 持有共享排他锁的事务可以读取表中的数据，但不能修改数据。
- 其他事务可以读取表中的数据，但不能获取排他锁或请求修改表结构的操作。
- 如果有其他事务已经持有共享锁（如SELECT语句），则请求共享排他锁的事务会被阻塞，直到所有的共享锁被释放。
- 如果有其他事务已经持有排他锁（如UPDATE、DELETE等），则请求共享排他锁的事务会被阻塞，直到排他锁被释放。
3. 定位排查共享排他锁：
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype和mode列来识别共享排他锁。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。 - 使用第三方工具如pgAdmin或者pg_top等来监控和分析锁的情况。
4. 处置共享排他锁：
- 如果共享排他锁的持有时间较短，可以等待锁被释放。
- 如果共享排他锁的持有时间较长，影响了其他事务的执行，可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计，尽量减少长时间持有共享排他锁的操作，例如将ALTER TABLE等操作放在非高峰期执行。
- 调整锁超时参数，如
lock_timeout，设置合适的超时时间，以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式，尽量避免不必要的锁冲突。
5、模拟
--session 1
t1=# begin;
BEGIN
t1=*# lock table t1 in share row exclusive mode;
LOCK TABLE
t1=*#
--session 2
t1=# update t1 set name='wz' where id=1;
--session 3
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
------------+-----------------------+-------+---------+----------
virtualxid | ExclusiveLock | 56685 | t |
virtualxid | ExclusiveLock | 56121 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
relation | RowExclusiveLock | 56121 | f | 24611
relation | ShareRowExclusiveLock | 56685 | t | 24611
(6 rows)
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+---------------------------
----+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------+---------------
------------------------------+------------------------------
| | 54134 | | 10 | postgres | | | | | 2024-02-20 19:16:19.000608+08 |
| | | Activity | LogicalLauncherMain | | | | |
| logical replication launcher
| | 54133 | | | | | | | | 2024-02-20 19:16:19.00146+08 |
| | | Activity | AutoVacuumMain | | | | |
| autovacuum launcher
5 | postgres | 57443 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:49:10.794515+08 | 2024-03-18 14:56:58.358838
+08 | 2024-03-18 14:56:58.358838+08 | 2024-03-18 14:56:58.35884+08 | | | active | | 930 | | select * from
pg_stat_activity; | client backend
24595 | t1 | 56685 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:47:17.344158+08 | 2024-03-18 14:48:51.503435
+08 | 2024-03-18 14:48:53.424111+08 | 2024-03-18 14:48:53.424306+08 | Client | ClientRead | idle in transaction | | | | lock table t1
in share row exclusive mode; | client backend
24595 | t1 | 56121 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:45:37.264456+08 | 2024-03-18 14:48:57.319499
+08 | 2024-03-18 14:48:57.319537+08 | 2024-03-18 14:48:57.319537+08 | Lock | relation | active | | 930 | | update t1 set
name='wz' where id=1; | client backend
| | 54130 | | | | | | | | 2024-02-20 19:16:18.996776+08 |
| | | Activity | BgWriterHibernate | | | | |
| background writer
| | 54129 | | | | | | | | 2024-02-20 19:16:18.997351+08 |
| | | Activity | CheckpointerMain | | | | |
| checkpointer
| | 54132 | | | | | | | | 2024-02-20 19:16:19.000129+08 |
| | | Activity | WalWriterMain | | | | |
| walwriter
(8 rows)
postgres=# select pg_blocking_pids(56121);
pg_blocking_pids
------------------
{56685}
(1 row)
5、ACCESS EXCLUSIVE锁(排它锁):
1. 形成排他锁的情况：
- 当一个事务对表进行写操作时，如INSERT、UPDATE、DELETE等语句，会请求排他锁。
- 当一个事务对表进行DDL操作时，如ALTER TABLE、DROP TABLE等语句，会请求排他锁。
- 当一个事务显式地使用LOCK TABLE语句请求排他锁时。
2. 排他锁的影响：
- 持有排他锁的事务可以读取和修改表中的数据。
- 其他事务无法获取该表上的共享锁或排他锁，也无法读取或修改该表中的数据，直到持有排他锁的事务释放锁。
- 如果有其他事务已经持有共享锁（如SELECT语句），则请求排他锁的事务会被阻塞，直到所有的共享锁被释放。
3. 定位排查排他锁：
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype和mode列来识别排他锁。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。 - 使用第三方工具如pgAdmin或者pg_top等来监控和分析锁的情况。
4. 处置排他锁：
- 如果排他锁的持有时间较短，可以等待锁被释放。
- 如果排他锁的持有时间较长，影响了其他事务的执行，可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计，尽量减少长时间持有排他锁的操作，例如将大批量的写操作拆分为多个小批量操作。
- 调整锁超时参数，如
lock_timeout，设置合适的超时时间，以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式，尽量避免不必要的锁冲突。
5. 模拟排他锁：
--session 1
t1=# begin;
BEGIN
t1=*# lock table t1 in exclusive mode;
LOCK TABLE
--session 2
t1=*# insert into t1 values(2,'zw');
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | q
uery | backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+---------------------------
----+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------+---------------
-------------------+------------------------------
| | 54134 | | 10 | postgres | | | | | 2024-02-20 19:16:19.000608+08 |
| | | Activity | LogicalLauncherMain | | | | |
| logical replication launcher
| | 54133 | | | | | | | | 2024-02-20 19:16:19.00146+08 |
| | | Activity | AutoVacuumMain | | | | |
| autovacuum launcher
5 | postgres | 57443 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:49:10.794515+08 | 2024-03-18 15:02:49.054994
+08 | 2024-03-18 15:02:49.054994+08 | 2024-03-18 15:02:49.054996+08 | | | active | | 930 | | select * from
pg_stat_activity; | client backend
24595 | t1 | 56685 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:47:17.344158+08 | 2024-03-18 15:00:51.350741
+08 | 2024-03-18 15:01:01.310725+08 | 2024-03-18 15:01:01.31078+08 | Client | ClientRead | idle in transaction | | | | lock table t1
in exclusive mode; | client backend
24595 | t1 | 56121 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:45:37.264456+08 | 2024-03-18 15:00:15.735059
+08 | 2024-03-18 15:02:07.382543+08 | 2024-03-18 15:02:07.382544+08 | Lock | relation | active | | 930 | | insert into t1
values(2,'zw'); | client backend
| | 54130 | | | | | | | | 2024-02-20 19:16:18.996776+08 |
| | | Activity | BgWriterHibernate | | | | |
| background writer
| | 54129 | | | | | | | | 2024-02-20 19:16:18.997351+08 |
| | | Activity | CheckpointerMain | | | | |
| checkpointer
| | 54132 | | | | | | | | 2024-02-20 19:16:19.000129+08 |
| | | Activity | WalWriterMain | | | | |
| walwriter
(8 rows)
postgres=# select pg_blocking_pids(56121);
pg_blocking_pids
------------------
{56685}
(1 row)
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
------------+------------------+-------+---------+----------
virtualxid | ExclusiveLock | 56685 | t |
virtualxid | ExclusiveLock | 56121 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
relation | AccessShareLock | 56121 | t | 24611
relation | RowExclusiveLock | 56121 | f | 24611
relation | ExclusiveLock | 56685 | t | 24611
二、行级锁(Row-level Locks)
1、FOR UPDATE锁
-
FOR UPDATE锁的形成场景:
- 当一个事务使用SELECT FOR UPDATE语句查询数据时,会在查询结果集中的每一行上获取FOR UPDATE锁。
- FOR UPDATE锁通常用于实现悲观并发控制,确保事务在修改数据之前获取了排他访问权。
- 常见的场景包括:
- 读取数据并准备进行更新操作时,使用SELECT FOR UPDATE锁定要更新的行。
- 在事务中读取数据,并希望防止其他事务同时修改或删除这些数据时,使用SELECT FOR UPDATE锁定查询结果集。
-
FOR UPDATE锁的影响:
- 持有FOR UPDATE锁的行可以被其他事务读取,但不能被其他事务修改或删除。
- 如果一个事务尝试对持有FOR UPDATE锁的行进行修改或删除操作,它将被阻塞,直到持有FOR UPDATE锁的事务释放锁。
- 多个事务不能同时持有同一行上的FOR UPDATE锁,它们会相互阻塞。
- FOR UPDATE锁的持有时间从锁定行被查询开始,直到事务提交或回滚为止。
-
定位和排查FOR UPDATE锁:
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype、mode和pid列来识别FOR UPDATE锁和持有锁的事务。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。
- 使用
-
处置FOR UPDATE锁问题:
- 如果FOR UPDATE锁的持有时间较短,可以等待锁被释放。
- 如果FOR UPDATE锁的持有时间较长,影响了其他事务的执行,可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计,尽量减少长时间持有FOR UPDATE锁的操作,例如将长时间运行的事务拆分为多个短事务。
- 调整锁超时参数,如
lock_timeout,设置合适的超时时间,以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式,尽量避免不必要的锁冲突。
-
模拟FOR UPDATE锁:
--session 1
t1=*# select * from t1 where id=1 for update;
id | name
----+----------------------
1 | zw
(1 row)
--session 2
t1=# update t1 set name='zw' where id=
查看
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+---------------------------
----+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------+---------------
--------------------------+------------------------------
| | 54134 | | 10 | postgres | | | | | 2024-02-20 19:16:19.000608+08 |
| | | Activity | LogicalLauncherMain | | | | |
| logical replication launcher
| | 54133 | | | | | | | | 2024-02-20 19:16:19.00146+08 |
| | | Activity | AutoVacuumMain | | | | |
| autovacuum launcher
5 | postgres | 57443 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:49:10.794515+08 | 2024-03-18 16:02:36.5801+0
8 | 2024-03-18 16:02:36.5801+08 | 2024-03-18 16:02:36.580102+08 | | | active | | 935 | | select * from
pg_stat_activity; | client backend
24595 | t1 | 56121 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:45:37.264456+08 | 2024-03-18 16:02:29.995787
+08 | 2024-03-18 16:02:29.995825+08 | 2024-03-18 16:02:29.995826+08 | Lock | transactionid | active | 936 | 935 | | update t1 set
name='zw' where id=1; | client backend
24595 | t1 | 71115 | | 10 | postgres | psql | | | -1 | 2024-03-18 15:30:44.725943+08 | 2024-03-18 16:02:14.179856
+08 | 2024-03-18 16:02:24.694908+08 | 2024-03-18 16:02:24.695065+08 | Client | ClientRead | idle in transaction | 935 | | | select * from
t1 where id=1 for update; | client backend
| | 54130 | | | | | | | | 2024-02-20 19:16:18.996776+08 |
| | | Activity | BgWriterHibernate | | | | |
| background writer
| | 54129 | | | | | | | | 2024-02-20 19:16:18.997351+08 |
| | | Activity | CheckpointerMain | | | | |
| checkpointer
| | 54132 | | | | | | | | 2024-02-20 19:16:19.000129+08 |
| | | Activity | WalWriterMain | | | | |
| walwriter
(8 rows)
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
---------------+------------------+-------+---------+----------
relation | RowShareLock | 71115 | t | 24611
virtualxid | ExclusiveLock | 71115 | t |
relation | RowExclusiveLock | 56121 | t | 24611
virtualxid | ExclusiveLock | 56121 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
transactionid | ShareLock | 56121 | f |
transactionid | ExclusiveLock | 71115 | t |
transactionid | ExclusiveLock | 56121 | t |
tuple | ExclusiveLock | 56121 | t | 24611
(10 rows)
postgres=# select pg_blocking_pids(56121);
pg_blocking_pids
------------------
{71115}
(1 row)
--session 3
postgres=# select pg_terminate_backend(71115);
pg_terminate_backend
----------------------
t
(1 row)
--session 2
t1=# update t1 set name='zw' where id=1;
UPDATE 1
2、FOR NO KEY UPDATE锁
-
FOR NO KEY UPDATE锁的形成场景:
- 当一个事务使用SELECT FOR NO KEY UPDATE语句查询数据时,会在查询结果集中的每一行上获取FOR NO KEY UPDATE锁。
- FOR NO KEY UPDATE锁通常用于实现悲观并发控制,确保事务在修改数据之前获取了排他访问权,但允许其他事务创建外键引用。
- 常见的场景包括:
- 读取数据并准备进行更新操作时,使用SELECT FOR NO KEY UPDATE锁定要更新的行,同时允许其他事务创建外键引用。
- 在事务中读取数据,并希望防止其他事务同时修改或删除这些数据,但允许创建外键引用时,使用SELECT FOR NO KEY UPDATE锁定查询结果集。
-
FOR NO KEY UPDATE锁的影响:
- 持有FOR NO KEY UPDATE锁的行可以被其他事务读取,但不能被其他事务修改或删除。
- 如果一个事务尝试对持有FOR NO KEY UPDATE锁的行进行修改或删除操作,它将被阻塞,直到持有FOR NO KEY UPDATE锁的事务释放锁。
- 多个事务不能同时持有同一行上的FOR NO KEY UPDATE锁,它们会相互阻塞。
- 与FOR UPDATE锁不同的是,FOR NO KEY UPDATE锁允许其他事务在锁定行上创建外键引用,而不会被阻塞。
- FOR NO KEY UPDATE锁的持有时间从锁定行被查询开始,直到事务提交或回滚为止。
-
定位和排查FOR NO KEY UPDATE锁:
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype、mode和pid列来识别FOR NO KEY UPDATE锁和持有锁的事务。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。
- 使用
-
处置FOR NO KEY UPDATE锁问题:
- 如果FOR NO KEY UPDATE锁的持有时间较短,可以等待锁被释放。
- 如果FOR NO KEY UPDATE锁的持有时间较长,影响了其他事务的执行,可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计,尽量减少长时间持有FOR NO KEY UPDATE锁的操作,例如将长时间运行的事务拆分为多个短事务。
- 调整锁超时参数,如
lock_timeout,设置合适的超时时间,以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式,尽量避免不必要的锁冲突。
-
模拟FOR NO KEY UPDATE
--session 1
t1=*# select * from t1 for no key update;
id | name
----+----------------------
1 | zw
(1 row)
--session 2
t1=# update t1 set name='yc' where id=1;
--session 3
t1=# select * from t1 for no key update;
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
---------------+------------------+-------+---------+----------
relation | RowShareLock | 82618 | t | 24611
virtualxid | ExclusiveLock | 82618 | t |
relation | RowExclusiveLock | 56121 | t | 24611
virtualxid | ExclusiveLock | 56121 | t |
relation | RowShareLock | 88614 | t | 24611
virtualxid | ExclusiveLock | 88614 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
transactionid | ExclusiveLock | 82618 | t |
tuple | ExclusiveLock | 88614 | f | 24611
transactionid | ShareLock | 56121 | f |
tuple | ExclusiveLock | 56121 | t | 24611
transactionid | ExclusiveLock | 56121 | t |
(13 rows)
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+---------------------------
----+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------+---------------
----------------------+------------------------------
| | 54134 | | 10 | postgres | | | | | 2024-02-20 19:16:19.000608+08 |
| | | Activity | LogicalLauncherMain | | | | |
| logical replication launcher
| | 54133 | | | | | | | | 2024-02-20 19:16:19.00146+08 |
| | | Activity | AutoVacuumMain | | | | |
| autovacuum launcher
5 | postgres | 57443 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:49:10.794515+08 | 2024-03-18 16:25:29.411073
+08 | 2024-03-18 16:25:29.411073+08 | 2024-03-18 16:25:29.411075+08 | | | active | | 949 | | select * from
pg_stat_activity; | client backend
24595 | t1 | 82618 | | 10 | postgres | psql | | | -1 | 2024-03-18 16:05:38.636406+08 | 2024-03-18 16:24:24.643109
+08 | 2024-03-18 16:24:36.730856+08 | 2024-03-18 16:24:36.73097+08 | Client | ClientRead | idle in transaction | 949 | | | select * from
t1 for no key update; | client backend
24595 | t1 | 56121 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:45:37.264456+08 | 2024-03-18 16:24:40.515319
+08 | 2024-03-18 16:24:40.515369+08 | 2024-03-18 16:24:40.515369+08 | Lock | transactionid | active | 950 | 949 | | update t1 set
name='yc' where id=1; | client backend
24595 | t1 | 88614 | | 10 | postgres | psql | | | -1 | 2024-03-18 16:23:25.77185+08 | 2024-03-18 16:24:44.978784
+08 | 2024-03-18 16:24:44.978784+08 | 2024-03-18 16:24:44.978786+08 | Lock | tuple | active | | 949 | | select * from
t1 for no key update; | client backend
| | 54130 | | | | | | | | 2024-02-20 19:16:18.996776+08 |
| | | Activity | BgWriterHibernate | | | | |
| background writer
| | 54129 | | | | | | | | 2024-02-20 19:16:18.997351+08 |
| | | Activity | CheckpointerMain | | | | |
| checkpointer
| | 54132 | | | | | | | | 2024-02-20 19:16:19.000129+08 |
| | | Activity | WalWriterMain | | | | |
| walwriter
(9 rows)
postgres=# select pg_blocking_pids(88614);
pg_blocking_pids
------------------
{56121}
(1 row)
postgres=# select pg_blocking_pids(56121);
pg_blocking_pids
------------------
{82618}
(1 row)
postgres=# select pg_blocking_pids(82618);
pg_blocking_pids
------------------
{}
(1 row)
3、FOR SHARE锁
-
FOR SHARE锁的形成场景:
- 当一个事务使用SELECT FOR SHARE语句查询数据时,会在查询结果集中的每一行上获取FOR SHARE锁。
- FOR SHARE锁通常用于实现悲观并发控制,确保事务在读取数据时获取了共享访问权,防止其他事务同时修改或删除这些数据。
- 常见的场景包括:
- 在事务中读取数据,并希望防止其他事务同时修改或删除这些数据时,使用SELECT FOR SHARE锁定查询结果集。
- 当多个事务需要同时读取相同的数据,但不允许同时修改或删除这些数据时,使用SELECT FOR SHARE锁定查询结果集。
-
FOR SHARE锁的影响:
- 持有FOR SHARE锁的行可以被其他事务读取,但不能被其他事务修改或删除。
- 如果一个事务尝试对持有FOR SHARE锁的行进行修改或删除操作,它将被阻塞,直到持有FOR SHARE锁的事务释放锁。
- 多个事务可以同时持有同一行上的FOR SHARE锁,它们之间不会相互阻塞。
- FOR SHARE锁允许其他事务在锁定行上创建外键引用,而不会被阻塞。
- FOR SHARE锁的持有时间从锁定行被查询开始,直到事务提交或回滚为止。
-
定位和排查FOR SHARE锁:
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype、mode和pid列来识别FOR SHARE锁和持有锁的事务。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。
- 使用
-
处置FOR SHARE锁问题:
- 如果FOR SHARE锁的持有时间较短,可以等待锁被释放。
- 如果FOR SHARE锁的持有时间较长,影响了其他事务的执行,可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计,尽量减少长时间持有FOR SHARE锁的操作,例如将长时间运行的事务拆分为多个短事务。
- 调整锁超时参数,如
lock_timeout,设置合适的超时时间,以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式,尽量避免不必要的锁冲突。
-
模拟FOR SHARE锁:
--session 1
t1=# begin;
BEGIN
t1=*# select * from t1 for share;
id | name
----+----------------------
1 | zw
(1 row)
--session 2
t1=*# update t1 set name='zw' where id=1;
--session 3
t1=*# select * from t1;
id | name
----+----------------------
1 | zw
(1 row)
t1=*# begin;
WARNING: there is already a transaction in progress
BEGIN
t1=*# select * from t1 for share;
id | name
----+----------------------
1 | zw
(1 row)
查看锁
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+---------------------------
----+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------+---------------
----------------------+------------------------------
| | 54134 | | 10 | postgres | | | | | 2024-02-20 19:16:19.000608+08 |
| | | Activity | LogicalLauncherMain | | | | |
| logical replication launcher
| | 54133 | | | | | | | | 2024-02-20 19:16:19.00146+08 |
| | | Activity | AutoVacuumMain | | | | |
| autovacuum launcher
5 | postgres | 57443 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:49:10.794515+08 | 2024-03-18 16:16:23.115689
+08 | 2024-03-18 16:16:23.115689+08 | 2024-03-18 16:16:23.11569+08 | | | active | | 938 | | select * from
pg_stat_activity; | client backend
24595 | t1 | 82618 | | 10 | postgres | psql | | | -1 | 2024-03-18 16:05:38.636406+08 | 2024-03-18 16:13:27.003727
+08 | 2024-03-18 16:13:29.771336+08 | 2024-03-18 16:13:29.771445+08 | Client | ClientRead | idle in transaction | 938 | | | select * from
t1 for share; | client backend
24595 | t1 | 56121 | | 10 | postgres | psql | | | -1 | 2024-03-18 14:45:37.264456+08 | 2024-03-18 16:13:35.123584
+08 | 2024-03-18 16:13:37.899357+08 | 2024-03-18 16:13:37.899359+08 | Lock | transactionid | active | 939 | 938 | | update t1 set
name='zw' where id=1; | client backend
24595 | t1 | 85869 | | 10 | postgres | psql | | | -1 | 2024-03-18 16:15:03.244609+08 | 2024-03-18 16:15:26.97929+
08 | 2024-03-18 16:15:53.171367+08 | 2024-03-18 16:15:53.171648+08 | Client | ClientRead | idle in transaction | 940 | | | select * from
t1 for share; | client backend
| | 54130 | | | | | | | | 2024-02-20 19:16:18.996776+08 |
| | | Activity | BgWriterHibernate | | | | |
| background writer
| | 54129 | | | | | | | | 2024-02-20 19:16:18.997351+08 |
| | | Activity | CheckpointerMain | | | | |
| checkpointer
| | 54132 | | | | | | | | 2024-02-20 19:16:19.000129+08 |
| | | Activity | WalWriterMain | | | | |
| walwriter
(9 rows)
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
---------------+------------------+-------+---------+----------
relation | AccessShareLock | 85869 | t | 24611
relation | RowShareLock | 85869 | t | 24611
virtualxid | ExclusiveLock | 85869 | t |
relation | RowShareLock | 82618 | t | 24611
virtualxid | ExclusiveLock | 82618 | t |
relation | RowExclusiveLock | 56121 | t | 24611
virtualxid | ExclusiveLock | 56121 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
transactionid | ExclusiveLock | 82618 | t |
transactionid | ExclusiveLock | 85869 | t |
transactionid | ExclusiveLock | 56121 | t |
transactionid | ShareLock | 56121 | f |
tuple | ExclusiveLock | 56121 | t | 24611
(14 rows)
postgres=# select pg_blocking_pids(56121);
pg_blocking_pids
------------------
{82618}
(1 row)
4、FOR KEY SHARE
-
FOR KEY SHARE锁的形成场景:
- 当一个事务使用SELECT FOR KEY SHARE语句查询数据时,会在查询结果集中的每一行上获取FOR KEY SHARE锁。
- FOR KEY SHARE锁通常用于实现悲观并发控制,确保事务在读取数据时获取了共享访问权,防止其他事务同时修改或删除这些数据,同时允许其他事务在这些行上获取FOR KEY SHARE锁或FOR SHARE锁。
- 常见的场景包括:
- 在事务中读取数据,并希望防止其他事务同时修改或删除这些数据,但允许其他事务同时读取这些数据时,使用SELECT FOR KEY SHARE锁定查询结果集。
- 当多个事务需要同时读取相同的数据,并且允许其他事务同时获取FOR KEY SHARE锁或FOR SHARE锁时,使用SELECT FOR KEY SHARE锁定查询结果集。
-
FOR KEY SHARE锁的影响:
- 持有FOR KEY SHARE锁的行可以被其他事务读取,但不能被其他事务修改或删除。
- 如果一个事务尝试对持有FOR KEY SHARE锁的行进行修改或删除操作,它将被阻塞,直到持有FOR KEY SHARE锁的事务释放锁。
- 多个事务可以同时持有同一行上的FOR KEY SHARE锁,它们之间不会相互阻塞。
- FOR KEY SHARE锁允许其他事务在锁定行上获取FOR KEY SHARE锁或FOR SHARE锁,而不会被阻塞。
- FOR KEY SHARE锁的持有时间从锁定行被查询开始,直到事务提交或回滚为止。
-
定位和排查FOR KEY SHARE锁:
- 使用
pg_locks系统视图查询当前的锁信息。可以通过查询pg_locks视图中的locktype、mode和pid列来识别FOR KEY SHARE锁和持有锁的事务。 - 使用
pg_stat_activity系统视图查询当前活动的会话和它们的状态。可以通过查询pg_stat_activity视图中的state列来识别被阻塞的会话。 - 使用
pg_blocking_pids()函数获取阻塞指定进程的进程ID列表。
- 使用
-
处置FOR KEY SHARE锁问题:
- 如果FOR KEY SHARE锁的持有时间较短,可以等待锁被释放。
- 如果FOR KEY SHARE锁的持有时间较长,影响了其他事务的执行,可以考虑取消持有锁的事务。可以使用
pg_terminate_backend()函数来终止指定的后台进程。 - 优化应用程序的设计,尽量减少长时间持有FOR KEY SHARE锁的操作,例如将长时间运行的事务拆分为多个短事务。
- 调整锁超时参数,如
lock_timeout,设置合适的超时时间,以避免长时间的锁等待。 - 合理设计事务的隔离级别和访问模式,尽量避免不必要的锁冲突。
-
模拟FOR KEY SHARE锁:
--session 1
t1=# begin;
BEGIN
t1=*# select * from t1 for key share;
id | name
----+----------------------
1 | zw
(1 row)
--session 2
t1=# update t1 set name='zw' where id=1;
--session 3
t1=*# select * from t1 for share;
id | name
----+----------------------
1 | zw
(1 row)
t1=*# select * from t1 for key share;
id | name
----+----------------------
1 | zw
(1 row)
t1=*# select * from t1;
id | name
----+----------------------
1 | zw
(1 row)
t1=*# select * from t1 for update;
postgres=# SELECT locktype, mode, pid, granted,relation FROM pg_locks;
locktype | mode | pid | granted | relation
---------------+------------------+-------+---------+----------
relation | RowShareLock | 82618 | t | 24611
virtualxid | ExclusiveLock | 82618 | t |
relation | RowExclusiveLock | 56121 | t | 24611
virtualxid | ExclusiveLock | 56121 | t |
relation | RowShareLock | 88614 | t | 24611
virtualxid | ExclusiveLock | 88614 | t |
relation | AccessShareLock | 57443 | t | 12073
virtualxid | ExclusiveLock | 57443 | t |
transactionid | ShareLock | 88614 | f |
transactionid | ExclusiveLock | 82618 | t |
tuple | ExclusiveLock | 88614 | t | 24611
transactionid | ExclusiveLock | 56121 | t |
(12 rows)
6、兼容模式
Requested Lock Mode FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE
FOR KEY SHARE X
FOR SHARE X X
FOR NO KEY UPDATE X X X
FOR UPDATE X X X X
三、页级锁
这个锁与开发人员关系不大，主要用于数据块放入内存。类似latch: cache buffers chains、buffer pin、cache buffers lru chain 等。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
