【PostgreSQL】PostgreSQL中的锁--表锁
2022-06-15 08:58 abce 阅读(2777) 评论(0) 编辑 收藏 举报
postgres=# create table locktest(c int); CREATE TABLE postgres=# insert into locktest values(1),(2); INSERT 0 2 postgres=#
帮助视图
为了检查不同类型的锁，创建一个帮助视图：
CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted, CASE WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL THEN virtualxid || ' ' || transactionid WHEN virtualxid::text IS NOT NULL THEN virtualxid ELSE transactionid::text END AS xid_lock, relname, page, tuple, classid, objid, objsubid FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE -- do not show our view’s locks pid != pg_backend_pid() -- no need to show self-vxid locks virtualtransaction IS DISTINCT FROM virtualxid -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;
行共享锁(RowShareLock)
许多应用程序使用读取-修改-写回的方式。例如，应用程序从表中获取单个对象字段，修改数据，并将更改保存回数据库。在多用户环境中，不同的用户可能在此事务的过程中修改相同的行。仅仅使用简单的select，我们可能会获得不一致的数据。为了响应用户的需求，几乎所有的SQL数据库都有SELECT ... FOR SHARE锁定。此功能可防止应用程序实体在持有锁的事务提交或回滚之前修改数据。
例如：
1.有个用户有多个银行账户，存在一张账户表中，总额度(total_mount)记录在表bank_clients表中。
2.为了更新total_amount列，我们应该阻止所有与特定客户有关的记录被修改。
3.最好是使用单个update语句来估算total_amount，检索账户表中的记录。如果update需要外部数据，或需要操作其他用户，就需要多个语句了。
START TRANSACTION; SELECT * FROM accounts WHERE client_id = 55 FOR SHARE; SELECT * FROM bank_clients WHERE client_id=55 FOR UPDATE; UPDATE bank_clients SET total_amount=38984.33, client_status='gold' WHERE client_id=55; COMMIT;
select for share语句会创建一个行共享锁。
下面的语句会施加相同的行共享锁：
BEGIN; LOCK TABLE locktest IN ROW SHARE MODE;
无论查询涉及多少行记录，只会施加单个重量级的行共享锁
这在以下示例中通过未完成的事务来说明。启动未完成的事务，并在与数据库的第二个连接中从lockview 中查看：
BEGIN; SELECT * FROM locktest FOR SHARE; -- In second connection: postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+---------------+---------------+---------+----------+---------- 21144 | 3/13 | transactionid | ExclusiveLock | t | 586 | 21144 | 3/13 | relation | RowShareLock | t | | locktest
行排他锁(RowExclusiveLock)
修改行的实际查询还需要对表施加重量级锁，每个表一个。
下一个示例使用DELETE查询，但UPDATE具有相同的效果。
所有修改表中数据的命令都获得ROW EXCLUSIVE锁。
BEGIN; DELETE FROM locktest; -- second connection postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+---------------+------------------+---------+----------+---------- 10997 | 3/6 | transactionid | ExclusiveLock | t | 589 | 10997 | 3/6 | relation | RowExclusiveLock | t | | locktest
行排他锁与行共享锁不兼容，select * from locktest for share需要等待删除的事务结束或者回滚。
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname,page,tuple FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname | page | tuple -------+------+---------------+------------------+---------+----------+----------+------+------- 10997 | 3/6 | transactionid | ExclusiveLock | t | 589 | | | 10997 | 3/6 | relation | RowExclusiveLock | t | | locktest | | 11495 | 5/9 | relation | RowShareLock | t | | locktest | | 11495 | 5/9 | tuple | RowShareLock | t | | locktest | 0 | 1 11495 | 5/9 | transactionid | ShareLock | f | 589 | | |
修改表的语句同样要锁住所有的索引，即使索引中不包含被修改列。
-- preparation CREATE INDEX c_idx2 ON locktest (c); ALTER TABLE locktest ADD COLUMN c2 INT; CREATE INDEX c2_idx ON locktest(c2); -- unfinished example transaction BEGIN; UPDATE locktest SET c=3 WHERE c=1; -- second connection postgres=# SELECT * FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname | page | tuple | classid | objid | objsubid ------+--------+------------+------------------+---------+----------+----------+------+-------+---------+-------+---------- 3998 | 3/7844 | virtualxid | ExclusiveLock | t | 3/7844 | | | | | | 3998 | 3/7844 | relation | RowExclusiveLock | t | | c2_idx | | | | | 3998 | 3/7844 | relation | RowExclusiveLock | t | | c_idx | | | | | 3998 | 3/7844 | relation | RowExclusiveLock | t | | c_idx2 | | | | | 3998 | 3/7844 | relation | RowExclusiveLock | t | | locktest | | | | |
共享锁(ShareLock)
非并发版本的create index会阻止表更新。
BEGIN; CREATE INDEX c_idx ON locktest (c); -- second connection postgres=# SELECT * FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname | page | tuple | classid | objid | objsubid ------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+---------- 3998 | 3/7835 | virtualxid | ExclusiveLock | t | 3/7835 | | | | | | 3998 | 3/7835 | transactionid | ExclusiveLock | t | 564 | | | | | | 3998 | 3/7835 | relation | AccessExclusiveLock | t | | | | | | | 3998 | 3/7835 | relation | ShareLock | t | | locktest | | | | |
可以并发创建多个索引。
共享更新排他锁(SHARE UPDATE EXCLUSIVE)
数据库维护操作需要施加共享更新排他锁(SHARE UPDATE EXCLUSIVE)
·analyze table
·vacuum操作
·create index concurrently
analyze table会更新表的统计信息。
BEGIN; ANALYZE locktest; -- in second connection postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+---------------+--------------------------+---------+----------+---------- 10997 | 3/7 | transactionid | ExclusiveLock | t | 591 | 10997 | 3/7 | relation | ShareUpdateExclusiveLock | t | | locktest
RowExclusiveLock和ShareUpdateExclusiveLock 之间没有冲突。UPDATE/DELETE/INSERT 仍然可以在ANALYZE期间修改行。
VACUUM和CREATE INDEX CONCURRENTLY只能在事务之外执行。要在lockview中查看这些语句的效果，请先执行一个冲突事务，例如，在事务中运行ANALYZE，或者对一个巨大的表运行VACUUM。
CREATE INDEX CONCURRENTLY锁定可能会令人困惑。SHARE UPDATE EXCLUSIVE锁与用于DELETES、INSERT和UPDATES的ROW EXCLUSIVE锁不冲突。不幸的是，由于全表扫描，CREATE INDEX CONCURRENTLY 会两次等待活动事务完成：
“在并发索引构建中，索引实际上是在一个事务中进入系统目录的，然后在另外两个事务中发生两次表扫描。在每次表扫描之前，索引构建必须等待修改表的现有事务终止。” PostgreSQL 文档(https://www.postgresql.org/docs/9.1/sql-createindex.html)
访问排他锁(ACCESS EXCLUSIVE)
访问排他锁与其他任何锁都冲突。长用于以下语句：
·create rule
·drop table
·drop index
·truncate
·vacuum full
·lock table (default mode)
·cluster ·reindex
·refresh materialized view (without concurrently)
BEGIN; CREATE RULE r_locktest AS ON INSERT TO locktest DO INSTEAD NOTHING; -- second connection postgres=# select pid,vxid,lock_type,lock_mode,granted,xid_lock,relname from lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+---------------+---------------------+---------+----------+---------- 10997 | 3/19 | transactionid | ExclusiveLock | t | 596 | 10997 | 3/19 | relation | AccessExclusiveLock | t | | locktest
更重要的是，drop index需要对表和索引都使用访问排他锁：
BEGIN; DROP INDEX c_idx; -- second connection postgres=# SELECT * FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname | page | tuple | classid | objid | objsubid ------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+---------- 3998 | 3/7839 | virtualxid | ExclusiveLock | t | 3/7839 | | | | | | 3998 | 3/7839 | transactionid | ExclusiveLock | t | 569 | | | | | | 3998 | 3/7839 | relation | AccessExclusiveLock | t | | c_idx | | | | | 3998 | 3/7839 | relation | AccessExclusiveLock | t | | locktest | | | | |
排他锁(ExclusiveLock)
lock table语句使用排他锁。
BEGIN; LOCK TABLE locktest IN EXCLUSIVE MODE; -- second connection postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+-----------+---------------+---------+----------+---------- 10997 | 3/21 | relation | ExclusiveLock | t | | locktest
保存点(savepoints)
Savepoint产生一个额外的具有新xid值的transactionid类型的ExclusiveLock。
BEGIN; SELECT * FROM locktest FOR SHARE; SAVEPOINT s1; SELECT * FROM locktest FOR UPDATE; -- second connection postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+---------------+-----------------+---------+----------+---------- 10997 | 3/37 | transactionid | ExclusiveLock | t | 602 | 10997 | 3/37 | transactionid | ExclusiveLock | t | 603 | 10997 | 3/37 | relation | AccessShareLock | t | | c_idx 10997 | 3/37 | relation | RowShareLock | t | | locktest
pg_advisory_lock
有时，应用开发人员需要在进程之间同步。在这样的系统内，会频繁地创建和移除锁。基于行锁实现的这种特殊系统更容易造成表膨胀。
顾问锁有很多功能：
·每个会话或每个事务 ·如果锁不可用则等待或立即返回false ·独占或共享 ·64 位或两个32位整数资源标识符
想象一下，我们有几个cron作业，并且应用程序应该防止同时运行同一个脚本。接下来，每个脚本都可以检查PostgreSQL中是否有针对特定整数作业标识符的锁：
postgres=# SELECT pg_try_advisory_lock(10); pg_try_advisory_lock ---------------------- t -- second connection postgres=# SELECT * FROM lockview; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname | page | tuple | classid | objid | objsubid ------+------+-----------+---------------+---------+----------+---------+------+-------+---------+-------+---------- 3998 | 3/0 | advisory | ExclusiveLock | t | | | | | 0 | 10 | 1 -- other connections SELECT pg_try_advisory_lock(10); pg_try_advisory_lock ---------------------- f
死锁
当查询永远不会完成时，任何具有多个锁的系统往往会出现死锁情况。解决此类问题的唯一方法：杀死一个被阻止的语句。更重要的是，死锁检测在PostgreSQL中是一个昂贵的过程。 仅当事务被锁了deadlock_timeout毫秒（默认为一秒后）时才会检查死锁。
下面是两个不同连接 A和B的死锁情况的说明：
任何死锁都是从锁等待开始的。
A: BEGIN; SELECT c FROM locktest WHERE c=1 FOR UPDATE; B: BEGIN; SELECT c FROM locktest WHERE c=2 FOR UPDATE; SELECT c FROM locktest WHERE c=1 FOR UPDATE;
可以借助视图pg_stat_activity来查看产生锁等待的语句和事务：
postgres=# SELECT pg_stat_activity.pid AS pid, query, wait_event, vxid, lock_type, lock_mode, granted, xid_lock FROM lockview JOIN pg_stat_activity ON (lockview.pid = pg_stat_activity.pid); pid | query | wait_event | vxid | lock_type | lock_mode | granted | xid_lock -------+----------------------------+---------------+------+---------------+---------------------+---------+---------- 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead | 3/43 | transactionid | ExclusiveLock | t | 605 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead | 3/43 | advisory | ExclusiveLock | t | 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead | 3/43 | relation | AccessShareLock | t | 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead | 3/43 | relation | RowShareLock | t | 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ExclusiveLock | t | 606 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | advisory | ExclusiveLock | t | 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation | AccessShareLock | t | 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation | RowShareLock | t | 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | tuple | AccessExclusiveLock | t | 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ShareLock | f | 605
这里，select for update在c=2的行产生了死锁：
SELECT c FROM locktest WHERE c=2 FOR UPDATE;
然后，PostgreSQL会将信息写入server日志：
2018-08-02 08:46:07.793 UTC [10997] ERROR: deadlock detected 2018-08-02 08:46:07.793 UTC [10997] DETAIL: Process 10997 waits for ShareLock on transaction 606; blocked by process 11495. Process 11495 waits for ShareLock on transaction 605; blocked by process 10997. Process 10997: select c from locktest where c=2 for update; Process 11495: select c from locktest where c=1 for update; 2018-08-02 08:46:07.793 UTC [10997] HINT: See server log for query details. 2018-08-02 08:46:07.793 UTC [10997] CONTEXT: while locking tuple (0,3) in relation "locktest" 2018-08-02 08:46:07.793 UTC [10997] STATEMENT: SELECT c FROM locktest WHERE c=2 FOR UPDATE; ERROR: deadlock detected DETAIL: Process 10997 waits for ShareLock on transaction 606; blocked by process 11495. Process 11495 waits for ShareLock on transaction 605; blocked by process 10997. HINT: See server log for query details. CONTEXT: while locking tuple (0,3) in relation "locktest"
可以看到，数据库自动中断了一个被阻塞的事务。
多路死锁(Multi-way deadlocks)
通常只有两个事务造成死锁。但是，在复杂的情况下，应用程序可能会有多个事务形成依赖循环导致死锁。
第一步：
A：锁定 row1，B 锁定 row2，C 锁定 row3
第2步：
A：尝试获取 row3，B：尝试获取 row1，C：尝试获取 row2
总结
·不要将DDL放在长事务中
·避免在频繁更新表的高负载期间执行DDL
·cluster命令需要在表和索引上施加排他访问锁
·监控postgresql的日志，检测死锁相关信息
