共计 10188 个字符，预计需要花费 26 分钟才能阅读完成。
这篇文章主要讲解了“PostgreSQL 中 APP 在涉及 locks 时需要注意的地方有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“PostgreSQL 中 APP 在涉及 locks 时需要注意的地方有哪些”吧！
测试数据:
[local]:5432 pg12@testdb=# drop table if exists tbl;
DROP TABLE
Time: 36.136 ms
[local]:5432 pg12@testdb=# create table tbl(id int,c1 varchar(20),c2 varchar(20));
CREATE TABLE
Time: 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 1000000
Time: 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 ms1: Never add a column with a default value
表上新增列时获取的锁是 AccessExclusiveLock, 会阻塞 RW(包括 SELECT), 为了尽快完成列的添加, 新增有默认值的列, 可拆分为新增列, 然后执行 UPDATE 语句以免出现 R 阻塞.
-- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.929 ms
[local]:5432 pg12@testdb=#* alter table tbl add column c3 varchar(20) default c3
ALTER TABLE
Time: 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 | 1541
locktype | relation
relation | tbl
mode | AccessExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/8
granted | t
fastpath | f
Time: 29.088 ms使用先添加列, 后更新默认值的方法
------ session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.330 ms
[local]:5432 pg12@testdb=#* alter table tbl add column c4 varchar(20);
ALTER TABLE
Time: 0.460 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 0.530 ms
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.199 ms
[local]:5432 pg12@testdb=#* update tbl set c4 = c4
UPDATE 1000000
Time: 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 | 1541
locktype | relation
relation | tbl
mode | RowExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/10
granted | t
fastpath | t
Time: 1.062 ms虽然更新耗费的时间远比直接 add column 设置默认值要大, 但锁等级是 RowExclusiveLock, 并不会阻塞读
2: Beware of lock queues, use lock timeouts
PG 中每一个锁都有一个队列, 在获取锁时如需等待存在冲突的其他锁, 则会阻塞. 可通过设置超时时间避免长时间的等待. 这样虽然会失败, 但可通过后台查询等方法获取数据库活动, 保持数据库可控.
------ session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.148 ms
[local]:5432 pg12@testdb=#* alter table tbl add column c5 varchar(20) default c3
ALTER TABLE
Time: 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 | 1541
locktype | relation
relation | tbl
mode | AccessExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/11
granted | t
fastpath | f
Time: 2.751 ms
------ session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.861 ms
[local]:5432 pg12@testdb=#* SET lock_timeout TO 1s
Time: 0.689 ms
[local]:5432 pg12@testdb=#* select * from tbl limit 1;
ERROR: canceling statement due to lock timeout
LINE 1: select * from tbl limit 1;
^
Time: 1001.031 ms (00:01.001)
[local]:5432 pg12@testdb=#! end;
ROLLBACK
Time: 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 10000000
Time: 32784.183 ms (00:32.784)普通模式创建索引
------ session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 29.276 ms
[local]:5432 pg12@testdb=#* create index idx_tbl_id on tbl(id);
CREATE INDEX
Time: 7261.828 ms (00:07.262)
[local]:5432 pg12@testdb=#*
------ session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 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 | 1628
locktype | relation
relation | tbl
mode | RowExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 5/13
granted | f
fastpath | f
-[ RECORD 2 ]------+-----------------
pid | 1541
locktype | relation
relation | tbl
mode | ShareLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/13
granted | t
fastpath | f
Time: 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 block
Time: 0.491 ms
[local]:5432 pg12@testdb=#!不启动事务, 直接执行
------ session 1
[local]:5432 pg12@testdb=# -- only blocks other DDL
create index CONCURRENTLY idx_tbl_id on tbl(id);
CREATE INDEX
Time: 9718.400 ms (00:09.718)
------ session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.373 ms
[local]:5432 pg12@testdb=#* insert into tbl(id) values(0);
INSERT 0 1
Time: 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 | 1541
locktype | relation
relation | tbl
mode | ShareUpdateExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/21
granted | t
fastpath | f
-[ RECORD 2 ]------+-------------------------
pid | 1701
locktype | relation
relation | tbl
mode | ShareUpdateExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 6/71
granted | t
fastpath | f
Time: 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 TABLE
Time: 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 12000000
Time: 59285.694 ms (00:59.286)把 add primary key 这一个动作拆解为先添加唯一索引, 再添加 primary key constraint 这两个动作.
------ session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.155 ms
[local]:5432 pg12@testdb=#* alter table tbl add primary key(id);
ALTER TABLE
Time: 10572.201 ms (00:10.572)
[local]:5432 pg12@testdb=#*
------ session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 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 | 1628
locktype | relation
relation | tbl
mode | RowExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 5/18
granted | f
fastpath | f
-[ RECORD 2 ]------+--------------------
pid | 1541
locktype | relation
relation | tbl
mode | ShareLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/28
granted | t
fastpath | f
-[ RECORD 3 ]------+--------------------
pid | 1541
locktype | relation
relation | tbl
mode | AccessExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/28
granted | t
fastpath | f
-[ RECORD 4 ]------+--------------------
pid | 1907
locktype | relation
relation | tbl
mode | ShareLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 6/127
granted | t
fastpath | f
Time: 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 INDEX
Time: 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 TABLE
Time: 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 | 1541
locktype | relation
relation | tbl
mode | AccessExclusiveLock
page |
tuple |
virtualxid |
transactionid |
virtualtransaction | 3/49
granted | t
fastpath | f
Time: 0.803 msvacuum full 请求的锁是 AccessExclusiveLock, 会阻塞读写, 在目前 vacuum full 并不智能的情况下, 手工发起对单个表的 vacuum full 会保险许多.
7: Avoid deadlocks by ordering commands
注意命令的顺序, 避免死锁
------ session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.440 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
DELETE 1
Time: 0.567 ms
[local]:5432 pg12@testdb=#*
------ session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.960 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 2;
DELETE 1
Time: 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 detected
DETAIL: 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感谢各位的阅读，以上就是“PostgreSQL 中 APP 在涉及 locks 时需要注意的地方有哪些”的内容了，经过本文的学习后，相信大家对 PostgreSQL 中 APP 在涉及 locks 时需要注意的地方有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
正文完
