S-lock and X-lock of database, two-stage lock protocol

There are two basic types of locking: exclusive lock (X lock) and shared lock (S lock). The so-called X lock is that when transaction T adds an X lock to data A, transaction T is only allowed to read and modify the data. The so-called S lock is that when transaction T adds S lock to data A, other transactions can only add S lock to data A, but not X lock, until T releases the S lock on A. If transaction T adds an S lock to the data object A, T can read A, but cannot update it (S lock is also called read lock). Before T releases the S lock on A, other transactions can add S lock to A, but not X lock, so that A can be read, but A cannot be updated.

Two-stage locking protocol:

The two-stage locking protocol of the database means that all transactions must lock and unlock data items in two phases.

1. Expansion phase

Before reading or writing to any data item, apply for and obtain the blockage of the data item.

two。 Contraction stage

In each transaction, all blocking requests must precede unlocking requests.

For example, transaction T follows a two-segment locking protocol, and its blocking protocol is:

BEGIN TRANSACTION

LOCK (A); READ A; A: = A + 100; WRITE A; LOCK (B); UNLOCK (A); READ (B), UNLOCK (B)

COMMIT

It can be proved that if all concurrent transactions follow the two-stage lock protocol, then any parallel scheduling strategy for these parallel transactions is serializable.

It should be noted that if all transactions executed concurrently follow the two-stage lock protocol, it is only a sufficient condition that the parallel scheduling strategy of these transactions can be serialized, not a necessary condition.

The difference between a two-stage locking protocol and an one-time locking method to prevent deadlocks:

The one-time locking method requires that the transaction must lock all the data items to be used at once, otherwise it cannot continue to run.

Obviously, the one-time locking method conforms to the two-stage locking protocol, but the two-stage locking protocol does not require locking all data items that need to be used at once, so transactions that abide by the two-stage locking protocol may deadlock.