申博官网直营网址

申博官网直营网址 > 数据库 > PostgreSQL > PostgreSQL DBA(74) - Locks(Row-Level):What You Should Know

jetbullonbet官网:PostgreSQL DBA(74) - Locks(Row-Level):What You Should Know

翻译 PostgreSQL 作者:husthxd 时间:2019-08-12 14:53:40 0 删除 编辑

本文简单介绍了PostgreSQL Locks(Row-Level)的基础知识,原文详见 Postgres Locking Revealed ,有所删减和调整.

PostgreSQL把锁分为三类,table-level, row-level and advisory locks.Table和Row级锁可以是显式或隐式锁,advisory locks通常是显式锁.显式锁在显式用户请求时获得,而隐式锁则通过标准SQL命令获得.

Row-level Locks
PG 9.4,存在4种类型的Row-level locks,分别是:
1.FOR UPDATE
该模式对SELECT返回的结果集上锁,防止这些行被其他事务locked, modified or deleted.也就是说,其他事务如果希望UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE 这些行,那么会被阻塞.
该锁在DELETE/UPDATE某些列(作为外键的unique index列)时也会请求.
session 1


[local]:5432 pg12@testdb=# ---- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.861 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* delete from t_lock where id = 1;
DELETE 0
Time: 2.340 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 0.436 ms
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.617 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* delete from t_lock where id = 2;
DELETE 4
Time: 2.521 ms
[local]:5432 pg12@testdb=#* select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053;
 pid  | locktype | relation |       mode       | page | tuple 
------+----------+----------+------------------+------+-------
 2097 | relation | t_lock   | RowExclusiveLock |      |      
(1 row)
Time: 3.210 ms
[local]:5432 pg12@testdb=# select pg_backend_pid();
 pg_backend_pid 
----------------
           2097
(1 row)

session 2


[local]:5432 pg12@testdb=# ---- session 2
[local]:5432 pg12@testdb=#* select pg_backend_pid();
 pg_backend_pid 
----------------
           2515
(1 row)
Time: 0.457 ms
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.875 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* delete from t_lock where id = 2;
##被阻塞

session 3
查询locks


[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053;
 pid  | locktype | relation |        mode         | page | tuple 
------+----------+----------+---------------------+------+-------
 2515 | relation | t_lock   | RowExclusiveLock    |      |      
 2097 | relation | t_lock   | RowExclusiveLock    |      |      
 2515 | tuple    | t_lock   | AccessExclusiveLock |    0 |     2
(3 rows)
Time: 3.098 ms

2.FOR NO KEY UPDATE
这种模式与FOR UPDATE类似,但强度要弱一些,该锁不会阻塞SELECT FOR KEY SHARE锁模式.在不需要请求FOR UPDATE锁执行UPDATE时获取该锁.
session 1


[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.392 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3;
UPDATE 4
Time: 3.190 ms
[local]:5432 pg12@testdb=#* select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053;
 pid  | locktype | relation |       mode       | page | tuple 
------+----------+----------+------------------+------+-------
 2097 | relation | t_lock   | RowExclusiveLock |      |      
(1 row)
Time: 3.007 ms

session 2


[local]:5432 pg12@testdb=# ---- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.885 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3;
-- 阻塞

session 3


[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple from pg_locks where relation=295053;
 pid  | locktype | relation |       mode       | page | tuple 
------+----------+----------+------------------+------+-------
 2515 | relation | t_lock   | RowExclusiveLock |      |      
 2097 | relation | t_lock   | RowExclusiveLock |      |      
 2515 | tuple    | t_lock   | ExclusiveLock    |    0 |     3
(3 rows)
Time: 3.104 ms

3.FOR SHARE
该模式与FOR NO KEY UPDATE类似,但该模式获取的是ShareLock而不是ExclusiveLock,这会阻塞其他事务在锁定的行上执行UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE,但不阻塞SELECT FOR SHARE or SELECT FOR KEY SHARE.

4.FOR KEY SHARE
该模式行为与FOR SHARE类似,但该锁更弱一些,阻塞 SELECT FOR UPDATE但不会阻塞SELECT FOR NO KEY UPDATE.该锁会阻塞其他事务执行DELETE和其他UPDATE更新key values的操作,但不会阻塞另外的UPDATE, nor SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

下面是row-level lock的冲突表:

参考资料
申博官网直营网址 Postgres Locking Revealed

来自 “ ITPUB博客 ” ,链接:/blog_itpub_net/6906/viewspace-2653394/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1343
  • 访问量
    3807368
申博138线上娱乐直营网 www.tyc88.com www.11psb.com www.22sbc.com 138申博体育在线娱乐直营网 申博会员网址
www.msc66.com 申博会员登入直营网 申博登陆网址 申博电子游戏备用网址 申博现金网直营网 申博游戏平台直营网
www.8888msc.com 申博游戏登录直营网 申博提款最快登入 申博娱乐网直营 申博游戏苹果手机能玩吗 申博娱乐太阳成登入