Postgresql锁机制详解(表锁和行锁)

表锁

LOCK [ TABLE ] [ ONLY ] name [ * ] [, …] [ IN lockmode MODE ] [ NOWAIT ]

lockmode包括以下几种：

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

LOCK TABLE命令用于获得一个表锁，获得进程将阻塞一直到等待的锁被其他事务释放。如果使用NOWAIT关键字则如果获得不到锁，将不会等待而是直接返回，放弃履行当前指令并抛出一个毛病(error)。一旦获得到锁，将一直持有锁直到事务结束。(没有主动释放锁的命令，锁总是会在事务结束的时候被释放)。

当使用自动获得锁的模式的时候，PostgreSQL总是尽量地使用限制最小的模式。LOCK TABLE命令使我们可以自己定义锁的限制大小。比如一个利用程序使用事务在读提交(Read Committed isolation level)模式下需要保证数据库的数据在事务期间保持稳定，因而可使用SHARE锁模式在读取前对表进行加锁。这可以避免并发的数据改变并且可以保证后续的事务对这个表的读取不会读到没有提交的数据，由于SHARE锁和由写入事务持有的ROW EXCLUSIVE锁是冲突的，所以对想要使用SHARE锁对表进行加锁的事务，将会等到它之前所有持有该表的ROW EXCLUSIVE锁的事务commit或是roll back。因此，一旦获得了表的SHARE锁，将不会有无提交的数据，一样也不会有其他事务能够对表数据进行改变，直到当前事务释放SHARE锁。

为了在REPEATABLE READ(重复读)模式和SERIALIZABLE(序列化)模式下实现一样的效果，一定要在任何查询和修改语句之前加上LOCK TABLE。在履行第一句SELECT语句或修改数据语句前，重复读和序列化模式中一个事务的的数据视图将会被存储为快照。在这类情况下，事务申明的表锁一样可以免并发的修改，但是其实不能保证该事务能够读取到最新提交的数据。

如果一个事务想要修改表中的数据，应当使用SHARE ROW EXCLUSIVE(同享行排他)锁而不是SHARE锁。同享行排他锁将能够保证在同一时间只有当前事务能够运行。不加这个锁的话可能会造成死锁：两个事务同时想要获得SHARE锁，并且接下来又想要同时获得ROW EXCLUSIVE锁去进行数据更新(注意：同一个事务获得的两种区别的锁不会造成冲突，所以对同一个事务，它可以在获得SHARE锁以后再次获得ROW EXCLUSIVE，固然是在没有其他事务获得SHARE锁的情况下)。为了不死锁，应当保证所有的事务获得同一对象的锁的顺序是一致的，同时如果在同一个对象上想要获得多个锁，则总是应当先获得限制最大的锁。

ACCESS SHARE（访问同享锁）

只与ACCESS EXCLUSIVE锁冲突。

SELECT命令会在当前查询的表上获得一个ACCESS SHARE锁。总的来讲，任何只读操作都会获得该锁。

ROW SHARE（行同享锁）

和EXCLUSIVE锁和ACCESS EXCLUSIVE锁冲突。

SELECT FOR UPDATE或SELECT FOR SHARE命令会在目标表上获得该锁，并且所有被援用但是没有FOR UPDATE的表上会加上ACCESS SHARED锁。

ROW EXCLUSIVE（行排他锁）

和SHARE，SHARE ROW EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

UPDATE，DELETE和INSERT会在目标表上获得该锁，总的来讲，任何对数据库数据进行修改的命令会获得到该锁。

SHARE UPDATE EXCLUSIVE（同享更新排他锁）

和SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE冲突，该锁可以保护表避免并发的(schema)改变和VACUUM(释放空间)命令。

VACUUM，ANALYZE，CREATE INDEX CONCURRENTLY和ALTER TABLE VALIDATE和其他ALTER TABLE类的命令会获得该锁。

SHARE（同享锁）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。该锁保护一个表避免并发的数据改变。

由CREATE INDEX命令取得。

SHARE ROW EXCLUSIVE（行同享排他锁）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE锁冲突，该锁用于保护一个表避免并发的数据改变，同时是自排他的，所以在同一时间只有同一个session可以持有该锁。

该锁不会被PGSQL的任何命令自动获得。

EXCLUSIVE（排它锁）

和ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。该锁只允许并发的ACCESS SHARE锁，只有只读操作能在一个事务持有排他锁的时候进行并发操作。

ACCESS EXCLUSIVE（访问排他锁）

和所有的锁都冲突，该锁保证只有持有锁的事务能够访问当前表。

被DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL和REFRESH MATERIALIZED VIEW命令自动获得。有很多种情势的ALTER TABLE命令可以获得该锁，它一样也是LOCK TABLE命令默许的锁级别。

只有ACCESS EXCLUSIVE锁可以避免一个SELECT语句。

注意

一段获得锁，只有当事务结束的时候才会释放，但是如果一个锁是在一个savepoint(保存点)以后被获得，则当这个保存点回滚的时候这个锁会被马上释放。

行锁

除表锁，PgSQL还提供了行锁。一个事务可以获得相互冲突的两种行锁，包括在子事务中，但是两个事务不能同时在同一行获得相互冲突的两种锁。

FOR UPDATE

FOR UPDATE锁使得SELECT语句可以获得行锁用于更新数据。这使得该行可以避免被其他的事务获得锁或进行更改删除操作，也就是说其他事务的操作会被阻塞直到当前事务结束；一样的，SELECT FOR UPDATE命令会等待直到前一个事务结束。在REPEATABLE模式或SERIALIZABLE模式下，如果一个将要被上锁的行在事务开始之前被删除，则会返回一个error。

FOR UPDATE锁一样可以被DELETE命令获得，和UPDATE命令当使用在肯定的行用来修改数据的时候也会获得到该锁。目前当使用肯定的唯一索引时使用UPDATE命令可以获得到该锁（部份索引和联合索引暂时不支持），但是未来可能会改变这类设计。

FOR NO KEY UPDATE

和FOR UPDATE命令类似，但是对获得锁的要求更加宽松一些，在同一行中不会阻塞SELECT FOR KEY SHARE命令。一样在UPDATE命令的时候如果没有获得到FOR UPDATE锁的情况下会获得到该锁。

FOR SHARE

和FOR NO KEY UPDATE命令类似，区别点在于这个锁是一个同享锁而不是之前的锁一样是排他锁，所以这个锁会阻塞UPDATE，DELETE，SELECT FOR UPDATE或SELECT FOR NO KEY UPDATE，但是不会阻塞SELECT FOR SHARE或SELECT FOR KEY SHARE。

FOR KEY SHARE

和FOR SHARE表现类似，但是对加锁的要求更加宽松，SELECT FOR UPDATE会被阻塞但是SELECT FOR NO KEY UPDATE不会被阻塞。KEY SHARE模式下的锁会阻塞其他事务的DELETE或是改变KEY值的UPDATE语句，但是对其他的UPDATE或是SELECT FOR NO KEY UPDATE，SELECT FOR SHARE和SELECT FOR KEY SHARE则不会阻塞。

补充：Postgresql死锁的处理

今天遇到一个奇怪的现象，select和delete表时正常履行，但truncate和drop表时会一直运行，也不报错。

查了些资料才发现问题的缘由，总结以下：

“drop table ” 和 “truncate table ” 需要申请排它锁 “ACCESS EXCLUSIVE “， 履行这个命令卡住时，说明此时这张表上还有操作正在进行，比如查询等，那末只有等待这个查询操作完成，”drop table” 或”truncate table”或增加字段的SQL 才能获得这张表上的 “ACCESS EXCLUSIVE” 锁 ，操作才能进行下去。

1.检索出死锁进程的ID。

SELECT * FROM pg_stat_activity WHERE datname=’死锁的数据库ID ‘;

检索出来的字段中，【wating 】字段，数据为t的那条，就是死锁的进程。找到对应的【procpid 】列的值。

2.将进程杀掉。

SELECT pg_cancel_backend(‘死锁那条数据的procpid值 ‘);

结果：运行完后，再次更新这个表，sql顺利履行。

如果pg_stat_activity 没有记录，则查询pg_locks会不会有这个对象的锁

另外pg_terminate_backend()函数也能够杀掉进程。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有毛病或未斟酌完全的地方，望不吝赐教。