Shared and exclusive locks in MySQL pessimistic lock

Overview:

Shared locks, also known as read locks, referred to as the S-lock, as the name implies, a shared lock is multiple transactions for the same data can share a lock, can access the data, but can only read can not be modified.

Exclusive locks, also known as write locks, referred to as the X-Lock, as the name implies, exclusive locks can not coexist with other, such as a transaction to obtain a data row of exclusive locks, other transactions can no longer acquire the row of other locks, including shared and exclusive locks, but the transaction to obtain an exclusive lock can be read and modify data on the row.

For shared lock everyone may be well understood, that is, multiple transactions can only read data can not change the data, for the exclusive lock everyone's understanding may be some difference, I made a mistake, thought that the exclusive lock a row of data, other transactions can not read and modify the row of data, in fact, it is not the case. An exclusive lock refers to a transaction that, after one row of data plus an exclusive lock, no other transaction can add another lock on it. MySQL InnoDB Engine Default modification data statement, Update,delete,insert automatically to the data involved with an exclusive lock, the SELECT statement does not add any lock type by default, if the exclusive lock can use the Select ... for UPDATE statement, The plus share lock can use the Select ... lock in share mode statement. Therefore, data rows that have been added to an exclusive lock cannot be modified in any other transaction type, nor can they be queried by the for update and lock in share mode lock, but directly through select ... from ... Query the data because there is no lock mechanism for normal queries.

Summarize:

1 exclusive locks and other locks cannot coexist

2innoDB engine. The default Update,delete,insert exclusive lock, the SELECT statement is not locked by default

3 shared locks can coexist with other locks

Test:

We have the following test data

Now we have an exclusive query for the id=1 data row, which uses begin to open the transaction without seeing me close the transaction, which is used for testing because the lock is freed by committing the transaction or rolling back the transaction.

Open a Query window

Will query to one piece of data, now open another query window, the same data using exclusive search and Shared lock query two ways to query

Exclusive Search

Shared Search

We see open Exclusive lock queries and shared lock queries are blocked because Id=1 's data has been added to an exclusive lock, where blocking is waiting for an exclusive lock to be released.

What if we use the following query directly?

We see that we can query the data.

Let's look at one more. A transaction acquires a shared lock, which can only be shared or unlocked in other queries.

We see that data can be queried, but an exclusive lock cannot be found because an exclusive lock cannot exist on the same data as a shared lock.

Finally, we verify that the above-mentioned MySQL InnoDB engine in the Update,delete,insert statement automatically add exclusive lock problem,

At this time the shared query is blocked, waiting for the release of the lock, but the normal query can find the data, because the use of locking mechanism is not mutually exclusive lock, but the data is to modify the data before the old data.

Then we submit the data, release the exclusive lock to see the modified data, at this time can be used exclusive check, shared check and ordinary query, because the transaction after committing the row of data release exclusive lock, the following is only the normal query, the other students to verify themselves.

You can see that the results are the same as expected.

Shared and exclusive locks in MySQL pessimistic lock