2023-01-12 数据库锁.md-CSDN博客

本文链接：https://blog.csdn.net/richar1/article/details/131331521

数据库锁

锁主要是为了保持数据库的一致性，锁可以阻止用户修改一行或者整张表数据一般用在并发较高的数据库中。
在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
数据库中有两种基本的锁：排它锁(Exclusive Locks)和共享锁(Share Locks)。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问。

LOCK指令格式

lightdb@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

URL: https://www.postgresql.org/docs/13/sql-lock.html

表级锁

两个事务在同一时刻不能在同一个表上持有相互冲突的锁。
同一个事务不会和自身冲突，比如，在同一个事务中可以在一个表上请求ACCESS EXCLUSIVE锁，然后接着请求ACCESS SHARE。
非冲突锁模式可以被对个事务同事持有，ACCESS SHARE就是非冲突锁模式，可以被多个事务同时持有，而ACCESS EXCLUSIVE属于自冲突，不能被多个事务同时持有
只有ACCESS EXCLUSIVE阻塞SELECT (不包含FOR UPDATE/SHARE语句)。

LOCK-表锁模式

1. ACCESS SHARE 
    只与EXCLUSIVE冲突
2. ROW SHARE (SELECT ... FOR UPDATE/SELECT ... FOR SHARE )
    与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突
3. ROW EXCLUSIVE (UPDTE/DELETE/INSERT)
    与SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和 ACCESS EXCLUSIVE锁模式冲突
4. SHARE UPDATE EXCLUSIVE
    与SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和 ACCESS EXCLUSIVE锁模式冲突。这个模式保护一个表不被并发模式改变和VACUUM
    VACUUM(不带FULL选项), ANALYZE, CREATE INDEX CONCURRENTLY和 ALTER TABLE VALIDATE和其他ALTER TABLE 变体
5. SHARE (CREATE INDEX)
    与ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE和 ACCESS EXCLUSIVE锁模式冲突。这个模式避免表的并发数据修改。
6. SHARE ROW EXCLUSIVE (ALTER TABLE)
   与ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和 ACCESS EXCLUSIVE锁模式冲突。这个模式避免表的并发数据修改。 并且是自我排斥的，因此每次只有一个会话可以拥有它。
7. EXCLUSIVE (REFRESH/MATERIALIZED/VIEW CONCURRENTLY)
    与 ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和 ACCESS EXCLUSIVE锁模式冲突。这个模式只允许并发ACCESS SHARE锁，也就是说， 只有对表的读动作可以和持有这个锁模式的事务并发执行。
8. ACCESS EXCLUSIVE (DROP TABLE/TRUNCATE/REINDEX/CLUSTER/VACUUM FULL)
    与所有模式冲突,这个模式保证其所有者(事务)是可以访问该表的唯一事务。

行级锁

一个事务可以在相同的行上持有冲突的锁，即使是在不同的子事务中,但是除此之外，两个事务在同一行上永远不能持有冲突的锁。
行级锁不影响对数据的查询，它们只阻塞对同一行的写入和锁定。

LOCK-行锁模式

1. FOR UPDATE 
   会锁定SELECT语句查询的行记录。  
   其他尝试UPDATE,DELETE,SELECT FOR UPDATE,SELECT FOR NO KEY UPDATE、 SELECT FOR SHARE或 SELECT FOR KEY SHARE这些行的事务将被阻塞， 直到当前事务结束
   SELECT FOR UPDATE 也会等待执行上面命令的事务释放锁
2. FOR NO KEY UPDATE
    类似FOR UPDATE,但是锁请求相对弱一些。这个锁不会阻塞在相同行上执行的SELECT FOR KEY SHARE命令
3. FOR SHARE
    类似FOR NO KEY UPDATE,该命令在每个检索行上请求一个共享锁。一个共享锁阻塞其他事务在这些行上执行UPDATE,DELETE,SELECT FOR UPDATE或 SELECT FOR NO KEY UPDATE
4. FOR KEY SHARE

Postgresql不会在内存里保存任何关于已修改行的信息，因此对一次封锁的行数没有限制。
不过，锁住一行会导致一次磁盘写，因为SELECT FOR UPDATE将修改选中的行以标记它们被锁住了， 所以会导致磁盘写。

测试

# 准备一张表
lightdb@postgres=# CREATE TABLE COMPANY(
    ID INT PRIMARY KEY     NOT NULL,
    NAME           TEXT    NOT NULL,
    AGE            INT     NOT NULL,
    ADDRESS        CHAR(50),
    SALARY         REAL,
    JOIN_DATE      DATE
);

# 插入一点数据
lightdb@postgres=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
lightdb@postgres=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1
lightdb@postgres=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1
lightdb@postgres=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2

共享锁(select ... in share mode)

共享模式下，开启多个事务都可以正常加锁，不会发生冲突

打开一个postgresql会话，开启一个事务，以share mode进行加锁

# 1.使用begin开启事务
lightdb@postgres=# begin;
BEGIN
# 2.以共享模式对company加锁
lightdb@postgres=*# lock table company in share mode;
LOCK TABLE
lightdb@postgres=*# select id,name from company where age=23;
 id | name  
----+-------
  3 | Teddy
(1 row)

新打开一个数据库会话，开启一个事务，同样以share mode进行加锁

lightdb@postgres=# begin;
BEGIN
lightdb@postgres=*# lock table company in share mode;
LOCK TABLE
lightdb@postgres=*# select id,name from company where age = 23;
 id | name  
----+-------
  3 | Teddy
(1 row)

查看当前所有锁

lightdb@postgres=*# select pid,locktype,mode,granted,database from pg_locks;
  pid  |  locktype  |      mode       | granted | database 
-------+------------+-----------------+---------+----------
 66471 | relation   | AccessShareLock | t       |    14198
 66471 | virtualxid | ExclusiveLock   | t       |         
 40840 | relation   | AccessShareLock | t       |    14198
 40840 | relation   | AccessShareLock | t       |    14198
 40840 | virtualxid | ExclusiveLock   | t       |         
 66471 | relation   | AccessShareLock | t       |    14198
 66471 | relation   | ShareLock       | t       |    14198
 40840 | relation   | AccessShareLock | t       |    14198
 40840 | relation   | ShareLock       | t       |    14198
(9 rows)

提交两个事务

lightdb@postgres=*# commit;
COMMIT

# 事务会话活跃时间有一定的限制，所以可能会出现以下情况，可以通过修改'idle_in_transaction_session_timeout'来更改
lightdb@postgres=*# commit;
FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

# 查看配置
lightdb@postgres=# \x 
lightdb@postgres=# select * from pg_settings where name in ('idle_in_transaction_session_timeout');
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | idle_in_transaction_session_timeout
setting         | 180000
unit            | ms
category        | Client Connection Defaults / Statement Behavior
short_desc      | Sets the maximum allowed duration of any idling transaction.
extra_desc      | A value of 0 turns off the timeout.
context         | user
vartype         | integer
source          | default
min_val         | 0
max_val         | 2147483647
enumvals        | 
boot_val        | 180000
reset_val       | 180000
sourcefile      | 
sourceline      | 
pending_restart | f

FOR UPDATE

打开一个连接会话,开启一个事务

lightdb@postgres=# begin;
BEGIN
lightdb@postgres=*# update company set age = 24 where name = 'Teddy'; #ROW EXCLUSIVE
UPDATE 1
lightdb@postgres=*# select * from company for update; #ROW SHARE
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
  3 | Teddy |  24 | Norway                                             |  20000 | 
(5 rows)
 
lightdb@postgres=*# select pid,locktype,mode,granted,database,virtualxid,virtualtransaction from pg_locks;
  pid  |   locktype    |       mode       | granted | database | virtualxid | virtualtransaction 
-------+---------------+------------------+---------+----------+------------+--------------------
 78770 | relation      | AccessShareLock  | t       |    14198 |            | 8/5870
 78770 | relation      | RowShareLock     | t       |    14198 |            | 8/5870
 78770 | relation      | RowExclusiveLock | t       |    14198 |            | 8/5870
 78770 | relation      | RowShareLock     | t       |    14198 |            | 8/5870
 78770 | relation      | RowExclusiveLock | t       |    14198 |            | 8/5870
 78770 | virtualxid    | ExclusiveLock    | t       |          | 8/5870     | 8/5870
 78770 | transactionid | ExclusiveLock    | t       |          |            | 8/5870
(7 rows)

新开一个连接会话，执行

# 在会话1中的事务没有提交的时候，当前select语句会被阻塞，等待会话1中的事务提交之后释放锁
lightdb@postgres=# select * from company for update;

提交会话1中的事务

# 在会话1中执行
lightdb@postgres=*# select pid,locktype,mode,granted,database,virtualxid,virtualtransaction from pg_locks;
pid   |   locktype    |        mode         | granted | database | virtualxid | virtualtransaction 
--------+---------------+---------------------+---------+----------+------------+--------------------
 113108 | relation      | AccessShareLock     | t       |    14198 |            | 8/7688
 113108 | relation      | RowShareLock        | t       |    14198 |            | 8/7688
 113108 | relation      | RowExclusiveLock    | t       |    14198 |            | 8/7688
 113108 | relation      | RowShareLock        | t       |    14198 |            | 8/7688
 113108 | relation      | RowExclusiveLock    | t       |    14198 |            | 8/7688
 113108 | virtualxid    | ExclusiveLock       | t       |          | 8/7688     | 8/7688
  78989 | relation      | RowShareLock        | t       |    14198 |            | 9/5672
  78989 | relation      | RowShareLock        | t       |    14198 |            | 9/5672
  78989 | virtualxid    | ExclusiveLock       | t       |          | 9/5672     | 9/5672
  78989 | transactionid | ShareLock           | f       |          |            | 9/5672 ----(ps:会话2)
  78989 | tuple         | AccessExclusiveLock | t       |    14198 |            | 9/5672
 113108 | transactionid | ExclusiveLock       | t       |          |            | 8/7688 ----(ps:会话1)
(12 rows)

# 查看锁冲突
lightdb@postgres=*# select * from pg_blocking_pids(78989);
pg_blocking_pids
------------------
{113108}
(1 row)


# 会话1中执行
lightdb@postgres=*# commit;

# 切换到会话2，可以发现查询结果返回
lightdb@postgres=# select * from company for update;
id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
  3 | Teddy |  24 | Norway                                             |  20000 | 
(5 rows)

# 如果加锁超时，会报错
lightdb@postgres=# select * from company for update;
ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (0,1) in relation "company"