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

This article mainly introduces "what are the basic knowledge of PostgreSQL Locks". In daily operation, I believe many people have doubts about the basic knowledge of PostgreSQL Locks. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the questions of "what is the basic knowledge of PostgreSQL Locks?" Next, please follow the editor to study!

PostgreSQL divides locks into three categories. Table-level, row-level and advisory locks.Table and Row level locks can be explicit or implicit, while advisory locks is usually explicit. Explicit locks are obtained at explicit user requests, while implicit locks are obtained through standard SQL commands.

Advisory Locks

PG provides an application-customized locks, which is called Advisory Locks-the system does not force the use of these locks and is used correctly by the application itself.

There are two ways to request to get advisory lock: Session Level and Transaction Level.

Session level: once a lock is acquired in session level, the lock will be held unless the session is terminated or explicitly released. Unlike standard lock requests, the advisory lock of session level does not need to comply with transaction semantics: the lock requested during the transaction will still be held after the rollback, and the unlock is valid even if the transaction call fails. Lock can be obtained multiple times, and for each complete lock request, there must be a corresponding unlock before releasing the lock.

Transaction level: similar to a normal lock, it is automatically released at the end of a transaction without explicit unlock operations.

For the same Advisory Lock, if both session leve and transaction level locks are requested, it will block in the desired manner.

Here is an example of Advisory Lock:

Session 1

[local]: 5432 pg12@testdb=#-- Transaction 1 [local]: 5432 pg12@testdb=# BEGIN;BEGINTime: 0.882 ms [local]: 5432 pg12@testdb=#* [local]: 5432 pg12@testdb=#* SELECT pg_advisory_xact_lock (1); pg_advisory_xact_lock-- (1 row) Time: 2.449 ms [local]: 5432 pg12@testdb=#*-Some work here [local]: 5432 pg12@testdb=#*

Session 2

[local]: 5432 pg12@testdb=#-- Transaction 2 [local]: 5432 pg12@testdb=# BEGIN;BEGINTime: 0.468 ms [local]: 5432 pg12@testdb=#* [local]: 5432 pg12@testdb=#* SELECT pg_advisory_xact_lock (1)

Session 3

[local]: 5432 pg12@testdb=# SELECT * FROM pg_locks where pid pg_backend_pid () Locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath-+- -- +-virtualxid | 5 amp 4 | | | | 5x4 | 1789 | ExclusiveLock | t | t virtualxid | 4x13 | 4x13 | 1787 | ExclusiveLock | t | t advisory | 16384 | | | 0 | 1 | 1 | 5 rows 4 | 1789 | ExclusiveLock | t | f advisory | 16384 | | 0 | 1 | 4 rows 13 | 1787 | ExclusiveLock | f | f (4 ms) Time: 3.748 ms |

By querying pg_locks, you can see that pid 1789 holds advisory lock (granted = t), while pid 1787 is waiting for the lock (granted = f).

At this point, the study of "what are the basic knowledge of PostgreSQL Locks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.