PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些-創(chuàng)新互聯(lián)
這篇文章主要講解了“PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些”吧！
測試數(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設(shè)置默認值要大,但鎖等級是RowExclusiveLock,并不會阻塞讀
2: Beware of lock queues, use lock timeouts
PG中每一個鎖都有一個隊列,在獲取鎖時如需等待存在沖突的其他鎖,則會阻塞.可通過設(shè)置超時時間避免長時間的等待.這樣雖然會失敗,但可通過后臺查詢等方法獲取數(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
回滾事務(wù)后,使用CONCURRENTLY模式創(chuàng)建索引,要注意的是CONCURRENTLY模式不能用在事務(wù)中
[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=#!
不啟動事務(wù),直接執(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
重新構(gòu)建測試數(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)過本文的學(xué)習(xí)后，相信大家對PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
本文題目：PostgreSQL中APP在涉及l(fā)ocks時需要注意的地方有哪些-創(chuàng)新互聯(lián)
鏈接URL：http://www.rwnh.cn/article0/ddspoo.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計公司、移動網(wǎng)站建設(shè)、企業(yè)建站、外貿(mào)網(wǎng)站建設(shè)、標簽優(yōu)化、品牌網(wǎng)站建設(shè)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 創(chuàng)新互聯(lián)：企業(yè)個性化定制網(wǎng)站的特點 2022-11-18
- 網(wǎng)站建設(shè)選自助建站，還是定制網(wǎng)站？我來給你整明白 2016-11-11
- 武漢定制網(wǎng)站：是什么原因讓你選擇了定制一個網(wǎng)站？ 2021-09-17
- 定制網(wǎng)站首頁設(shè)計怎樣才能吸引人？ 2022-12-30
- 企業(yè)為什么要定制網(wǎng)站設(shè)計制作呢 2017-08-29
- 怎么判定建站公司做的是模板還是定制網(wǎng)站呢？ 2016-06-17
- 定制網(wǎng)站建設(shè)如何獲得客戶?需要注意什么? 2022-10-14
- 定制網(wǎng)站有什么好處? 2023-02-12
- 定制網(wǎng)站建設(shè)需要解決的問題如何在創(chuàng)新中獲得用戶認可 2023-01-18
- 成都網(wǎng)站建設(shè)-成都定制網(wǎng)站制作的流程 2021-10-31
- 企業(yè)高端定制網(wǎng)站的優(yōu)勢 2021-03-31
- 模板建站與高端定制網(wǎng)站有什么不同 2023-02-28