Shulou(Shulou.com)06/01 Report--

Postgresql lock:

There are three lock modes in postgresql: spinlock, lwlook and regularlock.

1. Spinlock spin lock

Spinlock uses mutually exclusive information and is closely related to the operating system and hardware environment. The main feature of spinlocky is that the blocking time is very short and there is no waiting queue and deadlock detection mechanism. The spinlock lock cannot be released automatically when the transaction ends.

2. LWLock lightweight lock

LWLock mainly provides mutually exclusive access to the data structures of shared memory. The main features of LWLock are waiting queue and deadlock-free detection. Lwlock can be released automatically at the end of the transaction. Lwlock can be divided into exclusive mode and shared mode.

Exclusive mode: used for data modification operations, such as insert, update, or delete, to ensure that multiple updates are not made to the same resource at the same time.

Shared mode: used for reading data operations, allowing multiple transactions to read the same data, but not allowing other transactions to modify the current data, such as select statements.

3. Regularlock regular lock

Regularlock is the lock referred to in general database transaction management. The main features of regularlock are waiting queue, deadlock detection and automatic lock release.

There are 8 lock modes supported by regularlock, from low to high exclusive level: access share,row share,row exclusive,share update exclusive,share,share row exclusive,exclusive,access exclusive

①: access share (access shared lock): the query command (select) acquires the access shared lock on the queried table. In general, any read-only query operation on a table will acquire this type of lock. The lock of this mode conflicts with access exclusive (access exclusive lock).

②: row share (row shared lock): use the "select for update" or "select for share" command to get the row shared lock. In addition, this lock conflicts with exclusive (exclusive lock) and access exclusive (access exclusive lock).

③: row exclusive (row exclusive lock): using the update, delete, or insert command acquires row exclusive locks on the target table. And add access share locks to other referenced tables. In general, commands that change table data acquire row exclusive locks on this table. In addition, this lock conflicts with share (shared row exclusive lock), share row exclusive (shared row exclusive lock), exclusive (exclusive lock), and access exclusive (access exclusive lock).

④: share update exclusive (shared update exclusive lock): use shared update exclusive lock when using vacuum (without full option) analyze or create index concurrently statement.

⑤: share (shared lock): a shared lock is used when requested using the create index (without the concurrently option) statement.

⑥: share row exclusive (shared row exclusive lock): similar to an exclusive lock, but allows row sharing.

⑦: exclusive (exclusive lock): use exclusive locks when blocking line sharing and select for update.

⑧: access exclusive (access exclusive lock): the alter table,drop table,truncate,reindex,cluster or vacuum full command acquires the access exclusive lock. In the lock table command, access exclusive is the default mode if there is no other mode to live.

5. Deadlock:

In two or more tasks, if each task locks resources locked by other task views, these tasks are permanently blocked, resulting in a deadlock. The system is in a deadlock state.

Four necessary conditions for forming a deadlock:

①: request and hold conditions: the process of acquiring resources can request new resources at the same time.

②: non-deprivation condition: resources that have been allocated cannot be deprived from the process.

③: loop wait condition: multiple processes form a loop, and each process is waiting for the resources being consumed by neighboring processes.

④: mutex condition: resources can only be used by one process.

Ways to reduce deadlocks:

①: resources are used in the same order in all transactions.

②: make the transaction as simple as possible and in a batch.

③: set a reasonable range for deadlock timeout parameters, such as 3-30 minutes; if the timeout occurs, the operation is automatically abandoned to avoid suspending the process.

④: avoid user interaction in the transaction kernel and reduce the locking time of resources.

⑤: using a lower isolation level, compared to a higher isolation level, can effectively reduce the time it takes to hold shared locks and reduce competition between locks.

Example:

Use the command to lock a table. The syntax is as follows:

Lock [table] name [,...] [in lockmode mode] [nowait]

Where name is the name of the existing table to be locked; lockmode is the lock mode, which declares which locks conflict with; if no lock mode is declared, the default is access exclusive mode; nowait declares that lock table will not wait for the release of any conflicting locks, and if you have to wait for the required locks to be acquired, transactions will be launched.

Such as: lock table emp; or lock table aforme b

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.