PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些-創(chuàng)新互聯(lián)

這篇文章主要講解了“PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些”吧！

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：域名注冊、虛擬空間、營銷軟件、網(wǎng)站建設、巴里坤哈薩克網(wǎng)站維護、網(wǎng)站推廣。

測試數(shù)據(jù):

[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 ms

1: Never add a column with a default value
表上新增列時獲取的鎖是AccessExclusiveLock,會阻塞RW(包括SELECT),為了盡快完成列的添加,新增有默認值的列,可拆分為新增列,然后執(zhí)行UPDATE語句以免出現(xiàn)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中每一個鎖都有一個隊列,在獲取鎖時如需等待存在沖突的其他鎖,則會阻塞.可通過設置超時時間避免長時間的等待.這樣雖然會失敗,但可通過后臺查詢等方法獲取數(shù)據(jù)庫活動,保持數(shù)據(jù)庫可控.

------ 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';
SET
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模式創(chuàng)建Index.
新插入1000w數(shù)據(jù)

[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)

普通模式創(chuàng)建索引

------ 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模式創(chuàng)建索引,要注意的是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=#!

不啟動事務,直接執(zhí)行

------ 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模式創(chuàng)建索引,獲取的lock是ShareUpdateExclusiveLock,不會阻塞INSERT/UPDATE/DELETE操作(請求的鎖是RowExclusiveLock)

4: Take aggressive locks as late as possible
這個跟編程中定義變量類似 : 離需要用到的地方越近的地方才定義.文中的例子見仁見智,選擇使用.

5: Adding a primary key with minimal locking
重新構建測試數(shù)據(jù)

[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模式創(chuàng)建索引,與第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 ms

vacuum full請求的鎖是AccessExclusiveLock,會阻塞讀寫,在目前vacuum full并不智能的情況下,手工發(fā)起對單個表的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

產(chǎn)生死鎖

------ 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在涉及l(fā)ocks時需要注意的地方有哪些”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關知識點的文章，歡迎關注！

本文題目：PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些-創(chuàng)新互聯(lián)
鏈接URL：http://www.muchs.cn/article0/ddspoo.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設計公司、移動網(wǎng)站建設、企業(yè)建站、外貿(mào)網(wǎng)站建設、標簽優(yōu)化、品牌網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁設計