本文共 11580 字，大约阅读时间需要 38 分钟。
本节介绍了APP在涉及locks时需要注意的地方.
测试数据:
[local]:5432 pg12@testdb=# drop table if exists tbl;DROP TABLETime: 36.136 ms[local]:5432 pg12@testdb=# create table tbl(id int,c1 varchar(20),c2 varchar(20));CREATE TABLETime: 4.903 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# insert into tbl select x,'c1'||x,'c2'||x from generate_series(1,1000000) as x;INSERT 0 1000000Time: 3677.812 ms (00:03.678)[local]:5432 pg12@testdb=#
— session 1
[local]:5432 pg12@testdb=# select pg_backend_pid(); pg_backend_pid ---------------- 1541(1 row)
— session 2
[local]:5432 pg12@testdb=# select pg_backend_pid(); pg_backend_pid ---------------- 1628(1 row)Time: 4.446 ms
1: Never add a column with a default value
表上新增列时获取的锁是AccessExclusiveLock,会阻塞RW(包括SELECT),为了尽快完成列的添加,新增有默认值的列,可拆分为新增列,然后执行UPDATE语句以免出现R阻塞.-- session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 0.929 ms[local]:5432 pg12@testdb=#* alter table tbl add column c3 varchar(20) default 'c3';ALTER TABLETime: 32.881 ms[local]:5432 pg12@testdb=# -- session 2[local]:5432 pg12@testdb=# select * from tbl;-- 阻塞-- session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+--------------------pid | 1541locktype | relationrelation | tblmode | AccessExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/8granted | tfastpath | fTime: 29.088 ms
使用先添加列,后更新默认值的方法
------ session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 0.330 ms[local]:5432 pg12@testdb=#* alter table tbl add column c4 varchar(20);ALTER TABLETime: 0.460 ms[local]:5432 pg12@testdb=#* end;COMMITTime: 0.530 ms[local]:5432 pg12@testdb=# begin;BEGINTime: 0.199 ms[local]:5432 pg12@testdb=#* update tbl set c4 = 'c4';UPDATE 1000000Time: 5286.769 ms (00:05.287)[local]:5432 pg12@testdb=#* ------ session 2[local]:5432 pg12@testdb=# select * from tbl limit 1; id | c1 | c2 | c3 | c4 ----+-----+-----+----+---- 1 | c11 | c21 | c3 | (1 row)Time: 2.793 ms------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+-----------------pid | 1541locktype | relationrelation | tblmode | RowExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/10granted | tfastpath | tTime: 1.062 ms
虽然更新耗费的时间远比直接add column设置默认值要大,但锁等级是RowExclusiveLock,并不会阻塞读
2: Beware of lock queues, use lock timeouts
PG中每一个锁都有一个队列,在获取锁时如需等待存在冲突的其他锁,则会阻塞.可通过设置超时时间避免长时间的等待.这样虽然会失败,但可通过后台查询等方法获取数据库活动,保持数据库可控.------ session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 1.148 ms[local]:5432 pg12@testdb=#* alter table tbl add column c5 varchar(20) default 'c3';ALTER TABLETime: 2.726 ms[local]:5432 pg12@testdb=#* ------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+--------------------pid | 1541locktype | relationrelation | tblmode | AccessExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/11granted | tfastpath | fTime: 2.751 ms------ session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 0.861 ms[local]:5432 pg12@testdb=#* SET lock_timeout TO '1s';SETTime: 0.689 ms[local]:5432 pg12@testdb=#* select * from tbl limit 1;ERROR: canceling statement due to lock timeoutLINE 1: select * from tbl limit 1; ^Time: 1001.031 ms (00:01.001)[local]:5432 pg12@testdb=#! end;ROLLBACKTime: 0.984 ms[local]:5432 pg12@testdb=#!
3: Create indexes CONCURRENTLY
使用CONCURRENTLY模式创建Index. 新插入1000w数据[local]:5432 pg12@testdb=# insert into tbl select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,10000000) as x;INSERT 0 10000000Time: 32784.183 ms (00:32.784)
普通模式创建索引
------ session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 29.276 ms[local]:5432 pg12@testdb=#* create index idx_tbl_id on tbl(id);CREATE INDEXTime: 7261.828 ms (00:07.262)[local]:5432 pg12@testdb=#* ------ session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 0.358 ms[local]:5432 pg12@testdb=#* insert into tbl(id) values(0);-- 阻塞------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+-----------------pid | 1628locktype | relationrelation | tblmode | RowExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 5/13granted | ffastpath | f-[ RECORD 2 ]------+-----------------pid | 1541locktype | relationrelation | tblmode | ShareLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/13granted | tfastpath | fTime: 0.795 ms
回滚事务后,使用CONCURRENTLY模式创建索引,要注意的是CONCURRENTLY模式不能用在事务中
[local]:5432 pg12@testdb=#* -- only blocks other DDL[local]:5432 pg12@testdb=#* create index CONCURRENTLY idx_tbl_id on tbl(id);ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction blockTime: 0.491 ms[local]:5432 pg12@testdb=#!
不启动事务,直接执行
------ session 1[local]:5432 pg12@testdb=# -- only blocks other DDLcreate index CONCURRENTLY idx_tbl_id on tbl(id);CREATE INDEXTime: 9718.400 ms (00:09.718)------ session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 0.373 ms[local]:5432 pg12@testdb=#* insert into tbl(id) values(0);INSERT 0 1Time: 0.686 ms[local]:5432 pg12@testdb=#* ------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+-------------------------pid | 1541locktype | relationrelation | tblmode | ShareUpdateExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/21granted | tfastpath | f-[ RECORD 2 ]------+-------------------------pid | 1701locktype | relationrelation | tblmode | ShareUpdateExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 6/71granted | tfastpath | fTime: 0.754 ms
使用CONCURRENTLY模式创建索引,获取的lock是ShareUpdateExclusiveLock,不会阻塞INSERT/UPDATE/DELETE操作(请求的锁是RowExclusiveLock)
4: Take aggressive locks as late as possible
这个跟编程中定义变量类似 : 离需要用到的地方越近的地方才定义.文中的例子见仁见智,选择使用.5: Adding a primary key with minimal locking
重新构建测试数据[local]:5432 pg12@testdb=# truncate table tbl;TRUNCATE TABLETime: 91.815 ms[local]:5432 pg12@testdb=# insert into tbl select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,12000000) as x;INSERT 0 12000000Time: 59285.694 ms (00:59.286)
把add primary key这一个动作拆解为先添加唯一索引,再添加primary key constraint这两个动作.
------ session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 1.155 ms[local]:5432 pg12@testdb=#* alter table tbl add primary key(id);ALTER TABLETime: 10572.201 ms (00:10.572)[local]:5432 pg12@testdb=#* ------ session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 0.703 ms[local]:5432 pg12@testdb=#* insert into tbl(id) values(0);-- 阻塞------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+--------------------pid | 1628locktype | relationrelation | tblmode | RowExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 5/18granted | ffastpath | f-[ RECORD 2 ]------+--------------------pid | 1541locktype | relationrelation | tblmode | ShareLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/28granted | tfastpath | f-[ RECORD 3 ]------+--------------------pid | 1541locktype | relationrelation | tblmode | AccessExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/28granted | tfastpath | f-[ RECORD 4 ]------+--------------------pid | 1907locktype | relationrelation | tblmode | ShareLockpage | tuple | virtualxid | transactionid | virtualtransaction | 6/127granted | tfastpath | fTime: 1.397 ms
拆解后,使用CONCURRENTLY模式创建索引,与第3点类似
------ session 1[local]:5432 pg12@testdb=# -- takes a long time, but doesn’t block queries[local]:5432 pg12@testdb=# CREATE UNIQUE INDEX CONCURRENTLY idx_tbl_id ON tbl (id); CREATE INDEXTime: 9908.405 ms (00:09.908)[local]:5432 pg12@testdb=# -- blocks queries, but only very briefly[local]:5432 pg12@testdb=# ALTER TABLE tbl ADD CONSTRAINT pk_tbl PRIMARY KEY USING INDEX idx_tbl_id; NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "idx_tbl_id" to "pk_tbl"ALTER TABLETime: 4582.013 ms (00:04.582)
6: Never VACUUM FULL
------ session 1[local]:5432 pg12@testdb=# vacuum full;------ session 2------ session 3[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;-[ RECORD 1 ]------+--------------------pid | 1541locktype | relationrelation | tblmode | AccessExclusiveLockpage | tuple | virtualxid | transactionid | virtualtransaction | 3/49granted | tfastpath | fTime: 0.803 ms
vacuum full请求的锁是AccessExclusiveLock,会阻塞读写,在目前vacuum full并不智能的情况下,手工发起对单个表的vacuum full会保险许多.
7: Avoid deadlocks by ordering commands
注意命令的顺序,避免死锁------ session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 0.440 ms[local]:5432 pg12@testdb=#* delete from tbl where id = 1;DELETE 1Time: 0.567 ms[local]:5432 pg12@testdb=#* ------ session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 0.960 ms[local]:5432 pg12@testdb=#* delete from tbl where id = 2;DELETE 1Time: 1.783 ms[local]:5432 pg12@testdb=#* ------ session 3
产生死锁
------ session 1[local]:5432 pg12@testdb=#* delete from tbl where id = 2;------ session 2[local]:5432 pg12@testdb=#* delete from tbl where id = 1;ERROR: deadlock detectedDETAIL: Process 1628 waits for ShareLock on transaction 623; blocked by process 1541.Process 1541 waits for ShareLock on transaction 624; blocked by process 1628.HINT: See server log for query details.CONTEXT: while deleting tuple (0,1) in relation "tbl"Time: 1004.485 ms (00:01.004)[local]:5432 pg12@testdb=#! ------ session 3
参考资料
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-2653631/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-2653631/