核心内容摘要
从“原始模型”到“板子上真正跑起来”
事务、并发和锁什么是 ACID原子性Atomicity一致性Consistency隔离性Isolation持久性DurabilityDDL 事务在 PostgreSQL 中与其他数据库最大的不同是大多数 DDL 也是可以包含在一个事务中的而且也是可以回滚的。
适合场景PostgreSQL 作为 Sharding 分布式数据系统的底层数据库。
Sharding 中常常需要在多个节点中建相同的表此时可以考虑把建表语句放在同一个事务中这样就可以在各个节点上先启动一个事务然后再执行建表语句保证整个集群的一致性。
事务的
使用方法在 psql 的默认配置下自动提交功能AUTOCOMMIT是打开的。
可以通过设置 psql 中的内置变量AUTOCOMMIT来关闭自动提交功能\setAUTOCOMMIToff;BEGIN;SAVEPOINT保存点PostgreSQL 支持保存点SAVEPOINT的功能在一个大的事务中可以把操作过程分成几个部分第一个部分执行成功后可以建一个保存点若后面的部分执行失败则回滚到此保存点而不必回滚整个事务。
案例BEGIN;INSERTINTOtesttab01VALUES(
;INSERTINTOtesttab01VALUES(
;SAVEPOINTmy_savepoint01;INSERTINTOtesttab02VALUES(
;INSERTINTOtesttab02VALUES(
;-- 假设这里违反唯一约束报错如果发生错误只需回滚到上一个保存点ROLLBACKTOmy_savepoint01;事务隔离级别数据库的事务隔离级别有以下 4 种READ UNCOMMITTED读未提交READ COMMITTED读已提交REPEATABLE READ可重复读SERIALIZABLE串行化对于并发事务我们不希望发生不一致的情况这类情况的级别从高到低排序如下脏读不可重复读幻读PostgreSQL 中只支持三种隔离级别读已提交可重复读串行化需要说明在 PostgreSQL 的一个事务中不可能读到其他事务中未提交的数据。
在选择可重复读级别的时候实际上仍是可串行化所以实际的隔离级别可能比你选择的更加严格。
两阶段提交多台数据库之间的原子性就需要通过两阶段提交来实现两阶段提交是实现分布式事务的关键。
PostgreSQL 数据库支持两阶段提交协议。
两阶段提交协议的步骤应用程序调用事务协调器通知准备提交事务PostgreSQL 中一般是调用PREPARE TRANSACTION命令接收到PREPARE TRANSACTION命令保证将自己置于准备提交中的状态事务协调器接收所有数据库的响应如果任何一个数据库在第一阶段返回失败则事务协调器将会发一个回滚命令ROLLBACK PREPARED给各台数据库PostgreSQL 持久化事务案例前提max_prepared_transactions必须设置为一个大于 0 的数字。
-- 设置 max_prepared_transactions需在 postgresql.conf 中配置SETmax_prepared_transactions10;-- 如果配置文件中未设置此处会报错-- 创建表CREATETABLEtesttab01(idINTPRIMARYKEY);-- 启动事务BEGIN;INSERTINTOtesttab01VALUES(
;-- 准备事务PREPARETRANSACTIONglobal_trans_1;-- 全局事务 ID-- 模拟数据库重启-- pg_ctl stop -D $PGDATA-- pg_ctl start -D $PGDATA-- 连接数据库提交两阶段事务COMMITPREPAREDglobal_trans_1;-- 查询数据SELECT*FROMtesttab01;表级锁表级锁只有SHARE和EXCLUSIVE两种表级锁共有八种模式其存在于 PostgreSQL 的共享内存中可以通过pg_locks系统视图查询。
锁模式解释ACCESS SHARESELECT 命令在被引用的表上会获得一个这种模式的锁。
通常任何只读取表而不修改它的查询都将获取这种模式。
ROW SHARESELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上会获得一个这种模式的锁。
ROW EXCLUSIVEUPDATE、DELETE 和 INSERT 命令在目标表上会获得一个这种模式的锁。
SHARE UPDATE EXCLUSIVEALTER INDEX 和 ALTER TABLE 命令的变体会获得。
这种模式保护一个表不受并发模式改变的影响。
SHARECREATE INDEX不带 CONCURRENTLY命令会获得。
这种模式保护一个表不受并发数据改变的影响。
SHARE ROW EXCLUSIVECREATE TRIGGER 命令和某些形式的 ALTER TABLE 命令会获得。
EXCLUSIVEREFRESH MATERIALIZED VIEW CONCURRENTLY 命令会获得。
表锁特点锁粒度非常大。
通过类似于 MySQL 意向锁的方式PostgreSQL 中也是这样实现的如ROW SHARE、ROW EXCLUSIVE这两个锁。
行级锁行级锁模式比较简单只有两种共享锁排它锁在 PostgreSQL 中由于有多版本的实现所以实际读取行数据时并不会在行上执行任何锁。
表级锁命令 LOCK TABLELOCK[TABLE][ONLY]name[,...][INlockmodeMODE][NOWAIT];NOWAIT表示不等待锁如果无法立即获取锁则报错。
行级锁命令SELECT...FOR{UPDATE|SHARE}[OFtable_name[,...]][NOWAIT][...];NOWAIT关键字与在LOCK TABLE中相同加了NOWAIT后如果无法获得锁则直接报错而不会一直等待。
锁的查看pg_locks可以查询到当前锁的信息比如哪个事务被哪个事务阻塞了若执行一条 SQL 语句时阻塞住了需要查询为什么阻塞是谁阻塞住的。
名字类型引用描述locktypetext可锁定对象的类型relation, extend, page, tuple, transactionid, object, userlockdatabaseoidpg_database.oid对象所在的数据库的 OIDrelationoidpg_class.oid关系的 OIDpageinteger关系内部的页面编号tuplesmallint页面里面的元组编号transactionidxid事务的 IDclassidoidpg_class.oid包含该对象的系统表的 OIDobjidoid对象在其系统表内的 OIDobjsubidsmallint对于表的一个字段这是字段编号transactionxid持有此锁或者在等待此锁的事务的 IDpidinteger持有或者等待这个锁的服务器进程的进程 IDmodetext这个进程持有的或者是期望的锁模式grantedboolean如果持有锁为真如果等待锁为假表锁查询案例Session 1CREATETABLEtesttab02(idINTPRIMARYKEY);INSERTINTOtesttab02VALUES(
;BEGIN;LOCKTABLEtesttab02;查询锁SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;结果解释其中一行显示的是事务在自己的virtualxid上加的ExclusiveLock锁这是必定会加上的。
另一行才是我们实际在表上加的锁AccessExclusiveLock。
Session 2BEGIN;LOCKTABLEtesttab02;-- 将被阻塞查询锁SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;行锁查询案例Session 1SELECT*FROMtesttab02WHEREid1;BEGIN;SELECT*FROMtesttab02WHEREid1FORUPDATE;第三个窗口查询锁SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;问题 1为什么没有发现行锁答案实际上pg_locks并不能显示出每个行锁的信息原因也很简单行锁信息并不会记录到共享内存中。
问题 2在pg_locks中没有行锁信息如何知道一个进程被另一个进程的行锁阻塞了呢Session 2BEGIN;SELECT*FROMtesttab02WHEREid1FORUPDATE;-- 将被阻塞想查看哪个进程被阻塞住了只需要查看granted字段值为False的 PID。
SELECTpg_backend_pid();-- 查看当前会话的 PID通过pg_locks中granted false的记录可以找到被阻塞的进程。