postgresql-锁相关,postgresql如何查询锁表

摘要：

Postgresql并发控制目录Postgreql并发控制显示锁定八个表级锁的强度行级锁的四个行级锁强度页级锁顾问锁死锁Postgresql提供多种锁模式来控制对表中数据的并发访问。类似地，大多数PostgreSQL命令会自动应用适当的锁，以确保在命令执行期间不会以不兼容的方式删除或修改引用的表。任何PostgreSQL命令都不会自动请求此锁定模式。

postgresql-并发控制

postgresql-并发控制

显示锁定

PostgreSQL提供了多种锁模式用于控制表中数据的并发访问。这些模式可以用于在mvcc无法给出期望行为的场合。同样，大多数PostgreSQL命令自动施加恰当的锁以保证被引用的表在命令的执行过程中不会以一种不兼容的方式被删除或者修改。比如，在存在其它并发操作的时候，TRUNCATE是不能在同一个表上面执行的。

要检查数据库服务器里所有当前正在被持有的锁，可以使用pg_locks系统视图

表级锁

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

八种表级锁的强度

ACCESS SHARE： 只与ACCESS EXCLUSIVE冲突。 SELECT命令在引用的表上请求这个锁。通常，任何只读取表而不修改它的命令都请求这种锁模式。
ROW SHARE： 与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上需要一个这样模式的锁 (加上在所有被引用但没有ACCESS SHARE的表上的FOR UPDATE/FOR SHARE锁)。
ROW EXCLUSIVE： 与SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。 UPDATE,DELETE和INSERT命令自动请求这个锁模式(加上所有其它被引用的表上的ACCESS SHARE锁)。通常，这种锁将被任何修改表中数据的查询请求。
SHARE UPDATE EXCLUSIVE： 与SHARE UPDATE EXCLUSIVE,SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这个模式保护一个表不被并发模式改变和VACUUM。 VACUUM(不带FULL选项),ANALYZE,CREATE INDEX CONCURRENTLY和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锁模式冲突。这个模式避免表的并发数据修改。并且是自我排斥的，因此每次只有一个会话可以拥有它。任何PostgreSQL命令都不会自动请求这个锁模式。
EXCLUSIVE： 与ROW SHARE,ROW EXCLUSIVE,SHARE UPDATE EXCLUSIVE,SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这个模式只允许并发ACCESS SHARE锁，也就是说，只有对表的读动作可以和持有这个锁模式的事务并发执行。任何PostgreSQL命令都不会在用户表上自动请求这个锁模式。
ACCESS EXCLUSIVE： 与所有模式冲突(8大模式，也包括自身类型)。这个模式保证其所有者(事务)是可以访问该表的唯一事务。 ALTER TABLE,DROP TABLE,TRUNCATE,REINDEX,CLUSTER和VACUUM FULL命令要求这样的锁。多种形式的ALTER TABLE也要求一个该级别的锁 (参见 ALTER TABLE). 在LOCK TABLE命令没有明确声明需要的锁模式时，ACCESS EXCLUSIVE是缺省表锁模式。

冲突的锁模式

更新是获取的行排他所，那两个行排他所不冲突，为什么更新同一条数据的时候需要等待呢？

--初始化数据
abase=# create table t_lock_test(n_bh int,c_name varchar(300));
abase=# insert into t_lock_test select generate_series(1,100000),'zhangsan';
--事务1
abase=# begin; lock table t_lock_test in access exclusive mode;
BEGIN
LOCK TABLE

--事务2
abase=# select * from t_lock_test limit 100;
错误:  由于锁超时，取消语句操作
LINE 1: select * from t_lock_test limit 100;

一个请求获取了某种锁，那么这个锁模式将持续到事务结束。

我们常见的alter table，drop table，truncate，reindex，vacuum full都会获取到access exclusive锁，获取这种锁后，其它请求包括查询都会受到影响，这些操作在生产环境要谨慎操作。

行级锁

行锁语法(select ... for ...)：

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT ]
UPDATE |NO KEY UPDATE| SHARE |KEY SHARE
NOWAIT选项：如果查询出的行不能立即获得锁，那么语句将会立刻返回一个错误，而不是等待锁释放。

行锁使用(秒杀场景，或者订票)：

begin;
select 1 from tbl where id=pk for update nowait;  --  如果用户无法即刻获得锁，则返回错误。从而这个事务回滚。
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;
end;

四种行级锁的强度

FOR UPDATE： 令那些被SELECT检索出来的行被锁住，就像在更新一样。这样就避免它们在当前事务结束前被其它事务修改或者删除；也就是说，其它企图UPDATE,DELETE,SELECT FOR UPDATE,SELECT FOR NO KEY UPDATE,SELECT FOR SHARE或SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。同样，如果一个来自其它事务的UPDATE,DELETE,SELECT FOR UPDATE已经锁住了某个或某些选定的行，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。

1.
--for update
事务1
abase=# begin; select * from t_lock_test where n_bh < 2 for update;
BEGIN
 n_bh |  c_name  
------+----------
    1 | zhangsan
(1 row)

事务2
abase=# begin; update t_lock_test set c_name ='lisi' where n_bh = 1;
BEGIN
错误:  由于锁超时，取消语句操作
CONTEXT:  当更新关系"t_lock_test"的元组(0, 1)时
--事务1使用for update锁定n_bh<2的数据，当事务2去更新的时候就会等待事务1释放锁以后才能更新
2.
--for no key update
事务1
abase=# begin; select * from t_lock_test where n_bh = 2 for no key update;
BEGIN
 n_bh |  c_name  
------+----------
    2 | zhangsan
(1 row)
事务2
abase=# begin; update t_lock_test set c_name ='lisi' where n_bh = 2;
BEGIN
错误:  由于锁超时，取消语句操作
CONTEXT:  当更新关系"t_lock_test"的元组(0, 2)时
abase=# rollback;
ROLLBACK
abase=# begin; select * from t_lock_test  where n_bh = 2 for key share;
BEGIN
 n_bh |  c_name  
------+----------
    2 | zhangsan
(1 row)

select for share的锁和select for share的锁不会冲突. 但是和select for update冲突.

for update和 for update以及for share都冲突.

for share 的应用场景, 多个会话可能都要取这些被锁行的数据, 但是不允许任何人更改这些数据.

for update 的应用场景, 单一会话需要更改这些被锁行的数据. 其他会话如果要更改那么就等待.

锁粒度越细致, 并发写锁等待的概率就越低, 因此可以大大提高数据库的并发写能力.

行级锁可以是排他的或者是共享的。行级锁不影响对数据的查询；只是阻塞对同一行的写入。特定行上的排他行级锁是在行被更新的时候自动请求的。该锁一直保持到事务提交或者回滚。行级锁不影响对数据的查询，它们只阻塞对同一行的写入。

for update skip locked

页级锁

除了表级别的和行级别的锁以外，页面级别的共享/排他锁也用于控制对共享缓冲池中表页面的读/写访问。这些锁在抓取或者更新一行后马上被释放。应用程序员通常不需要关心页级锁，在这里提到它们只是为了完整。

咨询锁

pg中可以把锁分为三类：table-level，row-level and advisory locks. table 和row级锁可以是显示或者隐式锁，advisory locks通常是显示锁，显示锁在显示用户请求时获得，而隐式锁通过标准SQL命令获取

pg的读写是不冲突的，但有时候需要读写冲突那么就可以使用咨询锁（需求：数据正在被更新或者删除时，不允许被读取，秒杀等场景）

advisory lock分为session level和Transaction Level

session level:只有session终止或者显示释放锁，否则锁会一直存在，不会因为事务的结束而结束

transaction level:与普通锁类似，在事务结束时自动释放，没有显示的unlock操作

--正常更新一条数据读写是不相互影响的
会话1
abase=# begin; update t_lock_test set c_name = 'wangwu' where n_bh = 1;
BEGIN
UPDATE 1
会话2--可以读取到被更新前的数据
abase=# select * from t_lock_test where n_bh = 1;
 n_bh | c_name 
------+--------
    1 | lisi
(1 row)

--使用advisory lock锁住这个id
会话1
abase=# begin;update t_lock_test set c_name ='wangwu1' where n_bh = 1 returning pg_try_advisory_xact_lock(n_bh);
BEGIN
 pg_try_advisory_xact_lock 
---------------------------
 t
(1 row)

UPDATE 1
会话2
--会话2查询这条记录时，使用advisory lock探测这条记录，如果无法加锁，返回0条记录。从而实现读写阻塞（隔离）
abase=# select * from t_lock_test where n_bh = 1 and pg_try_advisory_xact_lock(1);
 n_bh | c_name 
------+--------
(0 rows)

advisory lock读写堵塞

使用advisory lock，实现了读写冲突的需求（实际上是让读的会话读不到被锁的记录）。要注意advisory lock锁住的ID是库级冲突的。

死锁

abase中出现死锁，设置了死锁超时时间默认是1s，当检测到死锁的时候数据库会kill一个进程，然后另外一个进程能够正常执行。

--会话1
abase=# begin; update t_lock_test set c_name ='lisi' where n_bh = '1';
BEGIN
UPDATE 1
--会话2
abase=# begin; update t_lock_test set c_name ='lisi' where n_bh = '2';
BEGIN
UPDATE 1
abase=# update t_lock_test set c_name ='lisi' where n_bh = '1';
UPDATE 1
--会话1
abase=# update t_lock_test set c_name ='lisi' where n_bh = '2';
错误:  检测到死锁
DETAIL:  进程7805等待在事务 2614474上的ShareLock; 由进程7453阻塞.
进程7453等待在事务 2614469上的ShareLock; 由进程7805阻塞.
HINT:  详细信息请查看服务器日志.
CONTEXT:  当更新关系"t_lock_test"的元组(0, 2)时

--日志报错：
检测到死锁	"进程7805等待在事务 2614474上的ShareLock; 由进程7453阻塞.
进程7453等待在事务 2614469上的ShareLock; 由进程7805阻塞.
进程 7805: update t_lock_test set c_name ='lisi' where n_bh = '2';
进程 7453: update t_lock_test set c_name ='lisi' where n_bh = '1';"

postgres中关于锁等待参数

1、lock_timeout =0

锁等待超时，语句在试图获取表，索引，行或其他数据库对象上单独锁时等到超过指定的毫秒数，默认0表示禁用

2、deadlock_timeout = 1s

在等待一个lock被释放的时间里，多久可以启动死锁检查机制。默认值1s，当发现死锁后会随机kill掉一个进程

总结

1.我们常见的alter table，drop table，truncate，reindex，vacuum full都会获取到access exclusive锁，获取这种锁后，其它请求包括查询都会受到影响，这些操作在生产环境要谨慎操作。

2.锁粒度越细致, 并发写锁等待的概率就越低, 因此可以大大提高数据库的并发写能力.

3.使用advisory lock，实现了读写冲突的需求（实际上是让读的会话读不到被锁的记录）。要注意advisory lock锁住的ID是库级冲突的。

免责声明：文章转载自《postgresql-锁相关》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

postgresql-锁相关

显示锁定

表级锁

八种表级锁的强度

行级锁

四种行级锁的强度

页级锁

咨询锁

死锁

postgres中关于锁等待参数

总结

相关文章

SQL Server 阻塞原因分析

MSSQL无落地文件执行Rootkit-WarSQLKit

mybatis 中 foreach collection的三种用法

[转载]oracle 存储过程的基本语法及注意事项

MyBatis(二)

oracle 排序后分页查询

最新文章

随机推荐

思享工具箱导航

JSON工具

格式化转换

加解密编码

文本数字

网络

站长

计算

其他

对照列表