CREATE TABLE test (
id int
);
CREATE TABLE
INSERT INTO test (
id
)
VALUES
(
1
);
INSERT 0 1
同时读写
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
UPDATE test SET id = id + 1 RETURNING *; | |
看到2 | SELECT * FROM test; |
看到1 | |
COMMIT; | COMMIT; |
SELECT * FROM test; | |
看到2 |
两个事务被打开。第一个更改一行,第二个可以继续读。它返回旧的行,也就是UPDATE
之前的行,这种行为被称为多版本并发控制。事务只能看到已经被写事务提交的数据。一个事务不能观察到由一个活跃连接造成的改变。
在PostgreSQL中,读写是可以共存的,写事务不会阻塞读事务。
同时写
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
UPDATE test SET id = id + 1 RETURNING *; | |
返回3 | UPDATE test SET id = id + 1 RETURNING *; |
等待事务1 | |
COMMIT; | 等待事务1 |
现在将重新读取,并且找到3、设置值,然后返回4 | |
COMMIT; |
PostgreSQL只会锁定被UPDATE
影响的行。因此如果有1000行,理论上用户可以在同一个表上运行1000个并发更改。
还值得提到的一点是,用户总是可以运行并发读,我们的两个写操作不会阻塞读。
典型错误和显式锁定
一个典型问题:
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT max(id) FROM test; | SELECT max(id) FROM test; |
看到4 | 看到4 |
用户决定用5 | 用户决定用5 |
INSERT INTO test (id) values (5); | INSERT INTO test (id) values (5); |
COMMIT; | COMMIT; |
如果id不是主键,那么将得到两个完全一样的项。
SELECT
*
FROM
test;
id
----
4
5
5
(3 rows)
如果id是主键,那么将会有重复键违规。
DELETE FROM
test
WHERE
id = 5;
DELETE 2
ALTER TABLE
test
ADD
PRIMARY KEY (id);
ALTER TABLE
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT max(id) FROM test; | SELECT max(id) FROM test; |
看到4 | 看到4 |
用户决定用5 | 用户决定用5 |
INSERT INTO test (id) values (5); | |
INSERT INTO test (id) values (5); | |
等待事务1 | |
COMMIT; | 等待事务1 |
ERROR: duplicate key value violates unique constraint "test_pkey" DETAIL: Key (id)=(5) already exists. | |
COMMIT; |
这个问题的两个变种都不是我们想要的。修正这个问题的一种方式是使用显式表锁定:
postgres=# \h LOCK
Command: LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
BEGIN;
LOCK TABLE
test
IN ACCESS EXCLUSIVE MODE;
INSERT INTO
test
SELECT
max(id) + 1
FROM product;
COMMIT;
锁机制
PostgreSQL为锁定一个表提供了8种类型的锁。一个锁可以轻如ACCESS SHARE
锁或者重如ACCESS EXCLUSIVE
锁。
表级锁类型
锁模式 | 解释 |
---|---|
Access Share | 只与Access Exclusive锁模式冲突。 |
SELECT将会在它查询的表上获取Access Share锁,一般地,任何一个对表上的只读查询操作都将获取这种类型锁。 | |
Row Share | 与Exclusive和Access Exclusive锁模式冲突。 |
SELECT FOR UPDATE和SELECT FOR SHARE命令将获得这种类型锁。 | |
Row Exclusive | 与Share,Share Row Exclusive,Exclusive,Access Exclusive模式冲突。 |
UPDATE/DELETE/INSERT命令会在目标表上获得这种类型的锁,一般地,更改表数据的命令都将在这张表上获得Row Exclusive锁。 | |
Share Update Exclusive | Share Update Exclusive,Share,Share Row Exclusive,Exclusive,Access exclusive模式冲突,这种模式保护一张表不被并发的模式更改和VACUUM。 |
VACUUM(without FULL),ANALYZE和CREATE INDEX CONCURRENTLY命令会获得这种类型锁。 | |
Share | 与Row Exclusive,Share Update Exclusive,Share Row Exclusive,Exclusive,Access exclusive锁模式冲突,这种模式保护一张表数据不被并发的更改。 |
CREATE INDEX命令会获得这种锁模式。 | |
Share Row Exclusive | 与Row Exclusive,Share Update Exclusive,Shared,Shared Row Exclusive,Exclusive,Access Exclusive锁模式冲突。 |
任何PostgreSQL命令都不会自动请求这个锁模式。 | |
Exclusive | 与ROW Share, Row Exclusive, Share Update Exclusive, Share , Share Row Exclusive, Exclusive, Access Exclusive模式冲突,这种锁模式仅能与Access Share模式并发,换句话说,只有读操作可以和持有Exclusive锁的事务并行。 |
任何PostgreSQL命令都不会在用户表上自动请求这个锁模式。 | |
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命令会获得这种类型锁,在LOCK TABLE命令中,如果没有申明其它模式,它也是默认模式。 |
表级锁的冲突矩阵
请求的锁模式 | 当前的锁模式 | |||||||
---|---|---|---|---|---|---|---|---|
Access Share | Row Share | Row Exclusive | Share Update Exclusive | Share | Share Row Exclusive | Exclusive | Access Exclusive | |
Access Share | X | |||||||
Row Share | X | X | ||||||
Row Exclusive | X | X | X | X | ||||
Share Update Exclusive | X | X | X | X | X | |||
Share | X | X | X | X | X | X | ||
Share Row Exclusive | X | X | X | X | X | X | ||
Exclusive | X | X | X | X | X | X | X | |
Access Exclusive | X | X | X | X | X | X | X | X |
表中“X”表示这两种表冲突,也就是不同的进程不能同时持有这两种锁。
最普通的是共享锁Share和排他锁Exclusive,它们分别是读、写锁的意思。加了Share锁,即读锁,表的内容就不能变化了,可以为多个事务加上此锁,只要任意一个事务不释放这个读锁,则其他事务就不能修改这个表。加上了Exclusive,相当于加了写锁,这时别的进程不能写也不能读这条数据。
后来数据库又加上了多版本的功能。修改一条语句的同时,允许了读数据,为了处理这种情况,又增加了两种锁Access Share和Access Excusive,锁中的关键字Access是与多版本读相关的。有了多版本的功能后,如果修改一行数据,实际并没有改原先那行数据,而是复制了一个新行,修改都在新行上,事务不提交,其他人是看不到修改的这条数据的。由于旧行数据没有变化,在修改过程中,读数据的人仍然可以读到旧的数据。
表级锁加锁对象是表,这使得加锁范围太大,导致并发并不高,于是人们提出了行级锁的概念,但行级锁与表级锁之间会产生冲突,这时需要一种机制来描述行级锁与表级锁之间的关系。方法就是当我们要修改表中的某一行数据时,需要先在表上加一种锁,如Row Share和Row Exclusive(对应MySQL中的共享意向锁和排他意向锁),表示即将在表的部分行上加共享锁或排他锁。
表级锁对应的操作
锁类型 | 对应的数据库操作 |
---|---|
Access Share | SELECT |
Row Share | SELECT FOR UPDATE,SELECT FOR SHARE |
Row Exclusive | UPDATE,DELETE,INSERT |
Share Update Exclusive | VACUUM (without FULL),ANALYZE,CREATE INDEX CONCURRENTLY |
Share | CREATE INDEX (without CONCURRENTLY) |
Share Row Exclusive | 任何PostgreSQL命令都不会自动请求这个锁模式 |
Exclusive | 任何PostgreSQL命令都不会在用户表上自动请求这个锁模式 |
Access Exclusive | ALTER TABLE,DROP TABLE,TRUNCATE,REINDEX,CLUSTER,VACUUM FULL |