PostgreSQL add 2 DML LOCK TUPLE MODE to 4
背景
本文主要是这篇博客的扩展,
使用数据库的朋友应该都认同这个观点: 锁粒度越细致, 并发写锁等待的概率就越低, 因此可以大大提高数据库的并发写能力.
PostgreSQL 在行锁级别提供了2种模式,SELECT FOR SHARE和SELECT FOR UPDATE.
select for share的锁和select for share的锁不会冲突. 但是和select for update冲突.
for update和 for update以及for share都冲突.
for share 的应用场景, 多个会话可能都要取这些被锁行的数据, 但是不允许任何人更改这些数据.
for update 的应用场景, 单一会话需要更改这些被锁行的数据. 其他会话如果要更改那么就等待.
现在PostgreSQL又添加了2个行锁类型, 分别是select for key share和select for no key update.
用法
FOR UPDATE, FOR NO KEY UPDATE/FOR SHARE/FOR KEY SHARE Clauses
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE are locking clauses; they affect how SELECT locks rows as they are obtained from the table.
The FOR UPDATE clause has this form:
FOR UPDATE [ OF table_name [, ...] ] [ NOWAIT ]
The FOR NO KEY UPDATE clause has this form:
FOR NO KEY UPDATE [ OF table_name [, ...] ] [ NOWAIT ]
The closely related FOR SHARE clause has this form:
FOR SHARE [ OF table_name [, ...] ] [ NOWAIT ]
Similarly, the FOR KEY SHARE clause has this form:
FOR KEY SHARE [ OF table_name [, ...] ] [ NOWAIT ]
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update.
This prevents them from being modified or deleted by other transactions until the current transaction ends.
That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE,
SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends.
The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns.
Currently, the set of columns considered for the UPDATE case are those that have an unique index on them that can be used
in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.
Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows,
SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted).
Within a REPEATABLE READ or SERIALIZABLEtransaction, however, an error will be thrown if a row to be locked has changed since the transaction started.
For further discussion see Chapter 13.
FOR NO KEY UPDATE behaves similarly, except that the lock acquired is weaker: this lock will not block
SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows.
FOR SHARE behaves similarly, except that it acquires a shared rather than exclusive lock on each retrieved row.
A shared lock blocks other transactions from performing UPDATE,DELETE, or SELECT FOR UPDATE on these rows,
but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.
FOR KEY SHARE behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.
A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE,
and neither it does prevent SELECT FOR UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.
测试
以下将针对前面那篇BLOG的测试场景, 重新测试一下增加了这2个锁模式后, FOREIGN TABLE更新的情况.
测试结果表明, 已经和ORACLE的测试一致. 不需要等待了.
测试数据 :
digoal=# CREATE TABLE A (
digoal(# AID integer not null,
digoal(# Col1 integer,
digoal(# PRIMARY KEY (AID)
digoal(# );
CREATE TABLE
digoal=#
digoal=# CREATE TABLE B (
digoal(# BID integer not null,
digoal(# AID integer not null,
digoal(# Col2 integer,
digoal(# PRIMARY KEY (BID),
digoal(# FOREIGN KEY (AID) REFERENCES A(AID)
digoal(# );
CREATE TABLE
digoal=# INSERT INTO A (AID) VALUES (1),(2);
INSERT 0 2
digoal=# INSERT INTO B (BID,AID) VALUES (2,1);
INSERT 0 1
digoal=# create extension pgrowlocks;
CREATE EXTENSION
场景1 :
SESSION A :
digoal=# begin;
BEGIN
digoal=# insert into b (aid,bid) values (1,1);
INSERT 0 1
SESSION B :
digoal=# select * from pgrowlocks('a');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+--------+-------------------+---------
(0,1) | 1706 | f | {1706} | {"For Key Share"} | {18172}
(1 row)
-- 注意锁模式是"For Key Share"
digoal=# select * from pgrowlocks('b');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+------+-------+------
(0 rows)
digoal=# select * from a where ctid='(0,1)';
aid | col1
-----+------
1 |
(1 row)
digoal=# update a set col1=22 where aid=1;
UPDATE 1
-- 更新A表的非键值无需等待
场景2 :
SESSION A :
digoal=# begin;
BEGIN
digoal=# update b set aid=2 where aid<>2;
UPDATE 2
SESSION B :
digoal=# select * from pgrowlocks('a');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+--------+-------------------+---------
(0,2) | 1708 | f | {1708} | {"For Key Share"} | {18172}
(1 row)
-- 注意锁模式是"For Key Share"
digoal=# select * from a where ctid='(0,2)';
aid | col1
-----+------
2 |
(1 row)
digoal=# update a set col1=22 where aid=2;
UPDATE 1
-- 更新A表的非键值无需等待
那么会话B更新A表的时候, 是什么锁呢
场景3 :
SESSION A :
digoal=# begin;
BEGIN
digoal=# update b set aid=2 where aid<>2;
UPDATE 2
SESSION B :
digoal=# begin;
BEGIN
digoal=# update a set col1=22 where aid=1;
UPDATE 1
digoal=# update a set col1=22 where aid=2;
UPDATE 1
SESSION C :
-- 一定要在第三个会话才能看到这些锁的状态.
-- 因为MVCC, 本地会话看到的是已经更新后的TUPLE, 新的tuple头上的infomask的信息.
-- 要看旧版本的infomask, 必须在其他会话查看.
digoal=# select * from pgrowlocks('a');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+-------------+-------------------------------+---------------
(0,3) | 1710 | f | {1710} | {Update} | {18332}
(0,4) | 3 | t | {1708,1710} | {"Key Share","No Key Update"} | {18172,18332}
(2 rows)
-- 注意会话B获取了1个No Key Update锁, 就是digoal=# update a set col1=22 where aid=2;这条产生的.
因为更新的不是键值相关的.
digoal=# select * from pgrowlocks('b');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+--------+----------+---------
(0,1) | 1708 | f | {1708} | {Update} | {18172}
(0,2) | 1708 | f | {1708} | {Update} | {18172}
(2 rows)
其他
1. 获取锁时遵循先获取轻量级锁的原则. 例如以下代码.
src/backend/access/heap/heapam.c
/*
* If we're not updating any "key" column, we can grab a weaker lock type.
* This allows for more concurrency when we are running simultaneously with
* foreign key checks.
*
* Note that if a column gets detoasted while executing the update, but the
* value ends up being the same, this test will fail and we will use the
* stronger lock. This is acceptable; the important case to optimize is
* updates that don't manipulate key columns, not those that
* serendipitiously arrive at the same key values.
*/
HeapSatisfiesHOTandKeyUpdate(relation, hot_attrs, key_attrs,
&satisfies_hot, &satisfies_key,
&oldtup, newtup);
if (satisfies_key)
{
*lockmode = LockTupleNoKeyExclusive;
mxact_status = MultiXactStatusNoKeyUpdate;
key_intact = true;
/*
* If this is the first possibly-multixact-able operation in the
* current transaction, set my per-backend OldestMemberMXactId setting.
* We can be certain that the transaction will never become a member of
* any older MultiXactIds than that. (We have to do this even if we
* end up just using our own TransactionId below, since some other
* backend could incorporate our XID into a MultiXact immediately
* afterwards.)
*/
MultiXactIdSetOldestMember();
}
else
{
*lockmode = LockTupleExclusive;
mxact_status = MultiXactStatusUpdate;
key_intact = false;
}
参考
2. http://www.postgresql.org/docs/devel/static/sql-select.html
3. src/include/access/heapam.h
/*
* Possible lock modes for a tuple.
*/
typedef enum LockTupleMode
{
/* SELECT FOR KEY SHARE */
LockTupleKeyShare,
/* SELECT FOR SHARE */
LockTupleShare,
/* SELECT FOR NO KEY UPDATE, and UPDATEs that don't modify key columns */
LockTupleNoKeyExclusive,
/* SELECT FOR UPDATE, UPDATEs that modify key columns, and DELETE */
LockTupleExclusive
} LockTupleMode;
4. src/backend/access/heap/README.tuplock
5. 4种锁的冲突表如下
KEY UPDATE UPDATE SHARE KEY SHARE
KEY UPDATE conflict conflict conflict conflict
UPDATE conflict conflict conflict
SHARE conflict conflict
KEY SHARE conflict
6. src/include/access/htup_details.h