What are the points that APP should pay attention to when it comes to locks in PostgreSQL 03/26 Update SLTechnology News&Howtos

What are the points that APP should pay attention to when it comes to locks in PostgreSQL

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly explains "what do you need to pay attention to when APP is involved in locks in PostgreSQL?" the content in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "what do you need to pay attention to when APP is involved in locks in PostgreSQL"?

Test data:

[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 local C1' | | x from generate_series (1m 1000000) as x insert 0 1000000Time: 3677.812 ms (00RV 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

When a new column is added to the table, the lock is AccessExclusiveLock, which will block the RW (including SELECT). In order to add the column as soon as possible, the new column with default value can be split into the new column, and then execute the UPDATE statement to avoid R blocking.

-- 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 crisis 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

Use the method of adding columns first and then updating default values

Although the update takes much more time than setting the default value by direct add column, the lock level is RowExclusiveLock and does not block reads

2: Beware of lock queues, use lock timeouts

Every lock in PG has a queue, which will block if you need to wait for other locks with conflicts. You can avoid a long wait by setting a timeout. Although this will fail, the database activity can be obtained by background query and other methods to keep the database controllable.

-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 investors 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 boxes: SETTime: 0.689 ms [local]: 5432 pg12@testdb=#* select * from tbl limit 1 political error: canceling statement due to lock timeoutLINE 1: select * from tbl limit 1; ^ Time: 1001.031 ms (00from tbl limit 01.001) [local]: 5432 pg12 testing databases! End;ROLLBACKTime: 0.984 ms [local]: 5432 pg12

3: Create indexes CONCURRENTLY

Use CONCURRENTLY mode to create Index.

Insert 1000W new data

[local]: 5432 pg12@testdb=# insert into tbl select x recorder c1' | | x recorder c2' | | x from generate_series (1meme 10000000) as x dexterity insert 0 10000000Time: 32784.183 ms (00 from generate_series 32.784)

Create index in normal mode

After rolling back the transaction, use the CONCURRENTLY mode to create the index. Note that the CONCURRENTLY mode cannot be used in the transaction

[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 testing databases!

Do not start the transaction, execute it directly

Create an index using CONCURRENTLY mode, and the obtained lock is ShareUpdateExclusiveLock and does not block the INSERT/UPDATE/DELETE operation (the requested lock is RowExclusiveLock)

4: Take aggressive locks as late as possible

This is similar to defining variables in programming: the closer you are to where you need to use them. The examples in this paper are of different opinions and choose to be used.

5: Adding a primary key with minimal locking

Rebuild test data

[local]: 5432 pg12@testdb=# truncate table tbl;TRUNCATE TABLETime: 91.815 ms [local]: 5432 pg12@testdb=# insert into tbl select XMagazine c2' | | Xmemery C3' | | x from generate_series (1Magazine 12000000) as xentinINSERT 0 12000000Time: 59285.694 ms (00VOID 59.286)

Disassemble the add primary key action into two actions: first add a unique index, then add primary key constraint.

After disassembling, create an index using CONCURRENTLY mode, similar to point 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 (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

6: Never VACUUM FULL

The lock of the vacuum full request is AccessExclusiveLock, which blocks read and write. When vacuum full is not intelligent at present, it is much safer to manually initiate vacuum full for a single table.

7: Avoid deadlocks by ordering commands

Pay attention to the order of commands to avoid deadlock

-session 1 [local]: 5432 pg12@testdb=# begin;BEGINTime: 0.440 ms [local]: 5432 pg12@testdb=#* delete from tbl where id = 1 pg12@testdb=#* 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 scape delete 1Time: 1.783 ms [local]: 5432 pg12@testdb=#*-session 3

Generate a deadlock

-session 1 [local]: 5432 pg12@testdb=#* delete from tbl where id = 2 blocked by process 1541.Process-session 2 [local]: 5432 pg12@testdb=#* delete from tbl where id = 1 blocked by process 1541.Process 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 (0jue 1) in relation "tbl" Time: 1004.485 ms (00in relation 01.004) [local]: 5432 pg12thanks for reading!-session 3 Thank you for reading. That's what APP needs to pay attention to when it comes to locks in PostgreSQL. After the study of this article, I believe that we have a deeper understanding of what we need to pay attention to when APP is involved in locks in PostgreSQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.