域名有了怎么建网站,吴江城乡住房和城乡建设局网站,中国建设网官网住房和城乡建设官网,wordpress 增加侧边栏一、概述 在 PostgreSQL 事务中提到#xff0c;多个用户访问相同数据时可能出现脏读#xff0c;不可重复度#xff0c;幻读#xff0c;更新丢失的问题#xff0c;为解决这些问题#xff0c;定义了不同的隔离级别#xff0c;而隔离级别的具体实现#xff0c;依靠的就是数…一、概述 在 PostgreSQL 事务中提到多个用户访问相同数据时可能出现脏读不可重复度幻读更新丢失的问题为解决这些问题定义了不同的隔离级别而隔离级别的具体实现依靠的就是数据库锁冲突的机制在数据库中不同事务在同一时刻不能对同一对象持有相互冲突的锁PostgreSQL 提供了多种锁模式用于控制对表中数据的并发访问大多数情况下数据库会在事务运行的过程中自动的给相关对象加锁解锁以保证事物的正常运行及数据的一致性。
二、分类
2.1 表级锁 两个事务在同一时刻不能在同一个表上持有相互冲突的锁非冲突锁可以由许多事务同时持有。特别注意一个事务决不会和自身冲突例如它可以在同一个表上获得 ACCESS EXCLUSIVE 锁然后接着获取 ACCESS SHARE 锁。表级锁冲突矩阵如下空白表示两种锁可相互兼容X 表示两种锁相互冲突。
ACCESS SHARE只与 ACCESS EXCLUSIVE 锁模式冲突。SELECT 命令在被引用的表上获得一个这种模式的锁。通常任何只读取表而不修改它的查询都将获得这种模式的锁。ROW SHARE与 EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。SELECT FOR UPDATE 和SELECT FOR SHARE 命令在目标表上取得一个这种模式的锁。ROW EXCLUSIVE与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。命令 UPDATE、DELETE 和 INSERT 在目标表上取得这种锁模式。通常这种锁模式将被任何修改表中数据的命令取得。SHARE UPDATE EXCLUSIVE与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发模式改变和 VACUUM 运行的影响。VACUUM不带FULL、ANALYZE、 CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、 CREATE STATISTICS 以及某些 ALTER INDEX 和 ALTER TABLE 的变体获得这种模式的锁。SHARE与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发数据改变的影响。CREATE INDEX不带 CONCURRENTLY将获得这种模式的锁。SHARE ROW EXCLUSIVE与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发数据修改所影响并且是自排他的这样在一个时刻只能有一个会话持有它。CREATE TRIGGER 和某些形式的 ALTER TABLE 将获得这种模式的锁。EXCLUSIVE与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式只允许并发的 ACCESS SHARE 锁即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。REFRESH MATERIALIZED VIEW CONCURRENTLY 将获得这种模式的锁。ACCESS EXCLUSIVE与所有模式的锁冲突ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE。这种模式保证持有者是访问该表的唯一事务。ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW不带CONCURRENTLY命令将获得这种模式的锁。很多形式的 ALTER INDEX 和 ALTER TABLE 也在这个层面上获得锁。这也是未显式指定模式的 LOCK TABLE 命令的默认锁模式。
示例 窗口 a 开启事务进行相关操作获取锁,窗口 b 查询当前持有的锁。
--窗口 a
postgres# begin;
BEGIN--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
----------------
(0 rows)上述可看到开启事务未进行任何操作时未持锁。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t; --新执行的操作a
---1
(1 row)--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
---------------------------t | AccessShareLock --新增的锁
(1 row)上述可看到事务中进行 select 操作时持有了 ACCESS SHARE 锁。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t;a
---1
(1 row)postgres*# select * from t for update; --新执行的操作a
---1
(1 row)
--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
---------------------------t | AccessShareLockt | RowShareLock --新增的锁
(2 rows)上述可看到事务中进行 select for update 操作时持有了 ROW SHARE 锁。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t;a
---1
(1 row)postgres*# select * from t for update;a
---1
(1 row)postgres*# update t set a 2 where a 1; --新执行的操作
UPDATE 1
--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
----------------------------t | AccessShareLockt | RowShareLockt | RowExclusiveLock --新增的锁
(3 rows)上述可看到事务中进行 update 操作时持有了 ROW EXCLUSIVE 锁。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t;a
---1
(1 row)postgres*# select * from t for update;a
---1
(1 row)postgres*# update t set a 2 where a 1;
UPDATE 1
postgres*# create index idx_t_a on t(a); --新执行的操作
CREATE INDEX--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
----------------------------t | AccessShareLockt | RowShareLockt | RowExclusiveLockt | ShareLock --新增的锁
(4 rows)上述可看到事务中进行 create index 操作时持有了 SHARE 锁。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t;a
---1
(1 row)postgres*# select * from t for update;a
---1
(1 row)postgres*# update t set a 2 where a 1;
UPDATE 1
postgres*# create index idx_t_a on t(a);
CREATE INDEX
postgres*# alter table t add b int; --新执行的操作
ALTER TABLE--窗口 b
postgres# select relation::regclass tab_name,mode from pg_locks where relation t::regclass;tab_name | mode
----------------------------t | AccessShareLockt | RowShareLockt | RowExclusiveLockt | ShareLockt | AccessExclusiveLock --新增的锁
(5 rows)上述可看到事务中进行 alter table 操作时持有了 ACCESS EXCLUSIVE 锁。
2.2 行级锁 除了表级锁以外还有行级锁两个事务不能在相同的行上持有冲突的锁一个事务的不同子事务除外。行级锁不影响数据查询它们只阻塞对同一行的写入者和加锁者。行级锁在事务结束时或保存点回滚的时候释放就像表级锁一样。行级锁冲突矩阵如下空白表示两种锁可相互兼容X 表示两种锁相互冲突。
FOR UPDATEFOR UPDATE 会导致由 SELECT 语句检索到的行被锁定就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除一直到当前事务结束。也就是说其他尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE 将被阻塞直到当前事务结束。反过来SELECT FOR UPDATE 将等待已经在相同行上运行以上这些命令的并发事务并且接着锁定并且返回被更新的行或者没有行因为行可能已被删除。不过在一个 REPEATABLE READ 或 SERIALIZABLE 事务中如果一个要被锁定的行在事务开始后被更改将会抛出一个错误。FOR NO KEY UPDATE行为与 FOR UPDATE 类似不过获得的锁较弱这种锁将不会阻塞尝试在相同行上获得锁的 SELECT FOR KEY SHARE 命令。任何不获取 FOR UPDATE 锁的 UPDATE 也会获得这种锁模式。FOR SHARE行为与 FOR NO KEY UPDATE 类似不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁会阻塞其他事务在这些行上执行 UPDATE、DELETE、SELECT FOR UPDATE 或者 SELECT FOR NO KEY UPDATE但是它不会阻止它们执行 SELECT FOR SHARE 或者 SELECT FOR KEY SHARE。FOR KEY SHARE行为与 FOR SHARE 类似不过锁较弱SELECT FOR UPDATE 会被阻塞但是 SELECT FOR NO KEY UPDATE 不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的 DELETE 或者 UPDATE但不会阻塞其他 UPDATE也不会阻止 SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者 SELECT FOR KEY SHARE。
示例 窗口 a 在事务中执行操作窗口 b 对同一对象执行操作查看是否冲突。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t for update;a
---1
(1 row)--窗口 b
postgres# select * from t for update;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while locking tuple (0,1) in relation t
postgres# select * from t for no key update;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while locking tuple (0,1) in relation t
postgres#
postgres# select * from t for share;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while locking tuple (0,1) in relation t
postgres#
postgres# select * from t for key share;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while locking tuple (0,1) in relation t上述可看到 for update 行锁同其他所有行锁冲突。
--窗口 a
postgres# begin;
BEGIN
postgres*# select * from t for key share;a
---1
(1 row)--窗口 b
postgres# select * from t for update;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while locking tuple (0,1) in relation t
postgres#
postgres# select * from t for no key update;a
---1
(1 row)postgres# select * from t for share;a
---1
(1 row)postgres# select * from t for key share;a
---1
(1 row)上述可看到 for key share 行锁仅同 for update 行锁冲突。
2.3 页级锁 除了表级别和行级别的锁以外页面级别的共享/排他锁被用来控制对共享缓冲池中表页面的读/写。 这些锁在行被抓取或者更新后马上被释放。应用开发者通常不需要关心页级锁我们在这里提到它们只是为了完整。
2.4 死锁 显式锁定的使用可能会增加死锁的可能性死锁是指两个或多个事务相互持有对方想要的锁。例如如果事务 1 在表 A 上获得一个排他锁同时试图获取一个在表 B 上的排他锁 而事务 2 已经持有表 B 的排他锁同时却正在请求表 A 上的一个排他锁那么两个事务就都不能进行下去。PostgreSQL 能够自动检测到死锁情况并且会通过中断其中一个事务从而允许其它事务完成来解决这个问题具体哪个事务会被中断是很难预测的而且也不应该依靠这样的预测。防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上获得锁。 示例
--窗口 a
postgres# begin;
BEGIN
postgres*# update t1 set a 2 where a 1;
UPDATE 1
postgres*# update t2 set a 11 where a 1;
UPDATE 1--窗口 b
postgres# begin;
BEGIN
postgres*# update t2 set a 2 where a 1;
UPDATE 1
postgres*# update t1 set a 22 where a 1;
ERROR: deadlock detected --此处触发了死锁自动处理机制中断了该事务。
DETAIL: Process 11771 waits for ShareLock on transaction 231938; blocked by process 11547.
Process 11547 waits for ShareLock on transaction 231940; blocked by process 11771.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,4) in relation t12.5 咨询锁 PostgreSQL 提供了一种方法创建由应用定义其含义的锁。这种锁被称为咨询锁因为系统并不强迫其使用而是由应用来保证其正确的使用。咨询锁可用于 MVCC 模型不适用的锁定策略其上限数量由 max_locks_per_transaction 和 max_connections 变量决定。 有两种方法在 PostgreSQL 中获取一个咨询锁 会话级别对于每一个完成的锁请求必须有一个相应的解锁请求直至锁被真正释放。 事务级别在事务结束时会自动释放它们并且没有显式的解锁操作。
操作咨询锁的函数
示例 两个窗口同时对一条数据执行删除插入操作时会存在如下问题
--窗口 a
postgres# create table t1(id int primary key);
CREATE TABLE
postgres#
postgres# insert into t1 values(1);
INSERT 0 1
postgres#
postgres# begin;
BEGIN
postgres*# delete from t1 where id 1;
DELETE 1
postgres*# insert into t1 values(1);
INSERT 0 1
postgres*#--窗口 b
postgres# begin;
BEGIN
postgres*# delete from t1 where id 1;DELETE 0 --窗口 a 事务未提交之前一直等待窗口 a 事务提交后显示删除 0 行。
postgres*# insert into t1 values(1);
ERROR: duplicate key value violates unique constraint t1_pkey
DETAIL: Key (id)(1) already exists. --插入数据报主键唯一约束错误。上述情况就可通过使用咨询锁避免:
--窗口 a
postgres# begin;
BEGIN
postgres*# select pg_try_advisory_xact_lock(1); --窗口 a 进行操作前获取咨询锁pg_try_advisory_xact_lock
---------------------------t
(1 row)postgres*# delete from t1 where id 1;
DELETE 1
postgres*# insert into t1 values(1);
INSERT 0 1
postgres*# commit;
COMMIT--窗口 b
postgres# begin;
BEGIN
postgres*# select pg_try_advisory_xact_lock(1); --窗口 b 进行操作前获取咨询锁失败,不能进行下一步操作。pg_try_advisory_xact_lock
---------------------------f
(1 row)postgres*# select pg_try_advisory_xact_lock(1); --窗口 b 在窗口 a 提交事务后获取咨询锁成功可进行下一步操作。pg_try_advisory_xact_lock
---------------------------t
(1 row)postgres*# delete from t1 where id 1;
DELETE 1
postgres*# insert into t1 values(1);
INSERT 0 1
postgres*# commit;
COMMIT
postgres# select * from t1;id
----1
(1 row)三、LOCK 显示锁定
3.1 语法
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]其中 lockmode 可以是以下之一ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE参数说明 name要锁定的一个现有表的名称可以是模式限定的。如果在表名前指定了 ONLY只有该表会被锁定。如果没有指定了 ONLY该表和它所有的后代表如果有都会被锁定。可选地在表名后指定 * 来显式地表示把后代表包括在内。命令LOCK TABLE a, b 等效于 LOCK TABLE a; LOCK TABLE b。这些表会被按照在 LOCK TABLE中指定的顺序一个一个被锁定。 lockmode锁模式指定这个锁和哪些锁冲突。如果没有指定锁模式那将使用最严格的模式 ACCESS EXCLUSIVE。 NOWAIT指定 LOCK TABLE 不等待任何冲突锁被释放如果所指定的锁不能立即获得那么事务就会中止。 3.2 示例
--在将要执行一次删除操作前在主键表上取一个 SHARE ROW EXCLUSIVE 锁
BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN(SELECT id FROM films WHERE rating 5);
DELETE FROM films WHERE rating 5;
COMMIT WORK;四、锁问题处理
4.1 排查
--查询持锁
SELECT
lock2.pid as locking_pid,
lock1.pid as locked_pid,
stat1.usename as locked_user,
stat1.query as locked_statement,
stat1.state as state,
stat2.query as locking_statement,
stat2.state as state,
now() - stat1.query_start as locking_duration,
stat2.usename as locking_user
FROM pg_catalog.pg_locks lock1
JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid stat1.pid
JOIN pg_catalog.pg_locks lock2 on
(lock1.locktype,lock1.database,lock1.relation,
lock1.page,lock1.tuple,lock1.virtualxid,
lock1.transactionid,lock1.classid,lock1.objid,
lock1.objsubid) IS NOT DISTINCT FROM
(lock2.locktype,lock2.DATABASE,
lock2.relation,lock2.page,
lock2.tuple,lock2.virtualxid,
lock2.transactionid,lock2.classid,
lock2.objid,lock2.objsubid)
JOIN pg_catalog.pg_stat_activity stat2 on lock2.pidstat2.pid
WHERE NOT lock1.granted AND lock2.granted;--查询行锁
with
t_wait as
( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pidb.pid and not a.granted
),
t_run as
( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pidb.pid and a.granted
),
t_overlap as
( select r.* from t_wait w join t_run r on ( r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid w.pid )
),
t_unionall as
( select r.* from t_overlap r union all select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
Pid: ||case when pid is null then NULL else pid::text end||chr(10)||
Lock_Granted: ||case when granted is null then NULL else granted::text end|| , Mode: ||case when mode is null then NULL else mode::text end|| , FastPath: ||case when fastpath is null then NULL else fastpath::text end|| , VirtualTransaction: ||case when virtualtransaction is null then NULL else virtualtransaction::text end|| , Session_State: ||case when state is null then NULL else state::text end||chr(10)||
Username: ||case when usename is null then NULL else usename::text end|| , Database: ||case when datname is null then NULL else datname::text end|| , Client_Addr: ||case when client_addr is null then NULL else client_addr::text end|| , Client_Port: ||case when client_port is null then NULL else client_port::text end|| , Application_Name: ||case when application_name is null then NULL else application_name::text end||chr(10)||
Xact_Start: ||case when xact_start is null then NULL else xact_start::text end|| , Query_Start: ||case when query_start is null then NULL else query_start::text end|| , Xact_Elapse: ||case when (now()-xact_start) is null then NULL else (now()-xact_start)::text end|| , Query_Elapse: ||case when (now()-query_start) is null then NULL else (now()-query_start)::text end||chr(10)||
SQL (Current SQL in Transaction): ||chr(10)||
case when query is null then NULL else query::text end,
chr(10)||--------||chr(10)
order by ( case mode when INVALID then 0 when AccessShareLock then 1 when RowShareLock then 2 when RowExclusiveLock then 3 when ShareUpdateExclusiveLock then 4 when ShareLock then 5 when ShareRowExclusiveLock then 6 when ExclusiveLock then 7 when AccessExclusiveLock then 8 else 0 end ) desc, (case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid;--查询超过 5s 的长事务
select usename,datname,state,wait_event_type,wait_event,xact_start,now(),query,pid,now()-xact_start as time
from pg_stat_activity
where now()-xact_startinterval 5 sec and query !~ ^COPY and stateidle
order by xact_start;4.2 解锁
--session消失事物回退需要superuser权限可以关闭所有的后台进程
select pg_terminate_backend(1445);--session还在事物回退只能关闭自己用户下的后台进程
select pg_cancel_backend(1445); --批量取消生成取消的SQL
WITH trans AS(SELECT pidFROM pg_stat_activityWHERE now()-xact_startinterval 10 secAND query !~ ^COPYAND STATEidleORDER BY xact_start)
SELECT select pg_cancel_backend || ( || trans.pid || );AS killsql
FROM trans;WITH trans AS (SELECT pidFROM pg_stat_activityWHERE now()-xact_startinterval 10 secAND query !~ ^COPYAND STATEidleORDER BY xact_start)
SELECT select pg_terminate_backend || ( || trans.pid || );AS killsql
FROM trans;
