Database row locks, table locks

Lock is mainly used in multi-user environment to ensure database integrity and consistency. We know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if there is no lock and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. These issues include: dirty reads, non-repeatable reads, and Phantom reads

Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct. (Workaround: Read Committed to ensure that the B transaction can read the data only after a transaction has submitted data)

Non-repeatable reading refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data and commits it. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read. (Workaround: Add a row of locks, a transaction in the operation of a few rows of data, the B transaction can only manipulate the data of other rows, locked a few lines can not be manipulated, thus avoiding the inconsistency of two reads.    MySQL uses this method by default. REPEATABLE READ repeatable)

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then, the user of the first transaction will see that there are no modified rows of data in the table, as if the illusion had occurred.  (Workaround: Lock the entire table only, with minimal overhead, but with minimal performance.) Serializable serialization)

READ UNCOMMITTED cannot avoid dirty reads, non-repeatable reads, and Phantom reads.

--------------------------------------------------------------------------------------------------------------

Row-level locks are the most granular lock in MySQL, and row-level locks can significantly reduce database operation conflicts. Row-level locks are divided into shared and exclusive locks, this article will detail the concept of shared locks and exclusive locks, how to use and precautions.

Shared Lock (Share lock)

A shared lock, also known as a read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction will be able to modify the data (to get an exclusive lock on the data) until all shared locks have been freed.

If transaction T adds a shared lock to data A, the other transaction can only have a plus shared lock, and cannot add an exclusive lock. Transactions that are allowed to share locks can read only data and cannot modify data.

Usage

SELECT ... LOCK IN SHARE MODE;

After the query statement is incremented LOCK IN SHARE MODE , MySQL adds a shared lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the shared lock can be successfully requested, otherwise it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of data.

Exclusive lock (EXclusive lock)

An exclusive lock is also called a write lock, and if the transaction T adds an exclusive lock to data A, other transactions can no longer block a with any type. A transaction that is granted an exclusive lock can read and modify data.

Usage

SELECT ... FOR UPDATE;

After the query statement is incremented FOR UPDATE , MySQL adds an exclusive lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, it can successfully request an exclusive lock or it will be blocked.

For INSERT, UPDATE, delete (add-in), InnoDB automatically adds an exclusive lock (X) to the data involved, and InnoDB does not add any locks to the general SELECT statement.

There is a table lock in MySQL,

LOCK TABLE my_tabl_name READ; Using a read lock table will block other transactions from modifying the table data.

LOCK TABLE my_table_name WRITE; Using a write lock table will block other transactions from reading and writing.

--------------------------------------------------------------------------------------------------------------- --

The purpose of the lock is the intention to "indicate" the intent of the lock: the main purpose of IX and is locks are to show this someone is locking a row, or going To lock a row in the table.

The intent lock is a table-level lock with two table locks in InnoDB:

Intent shared Lock (IS): Indicates that a transaction is ready to join a shared lock on a data row, which means that the table is locked before a shared lock is added to a data row

Intent exclusive Lock (IX): Similar to the above, indicates that the transaction is prepared to add an exclusive lock to the data row, that is, the transaction must first obtain the IX lock of the table before a data row is added to the exclusive lock.

Intent locks are innodb automatically and do not require user intervention.

Ix,is is a table-level lock that does not conflict with row-level x,s locks. Conflicts with table-level x,s only

The problem of coexistence of these two types of locks

Consider this example:

Transaction a locks a row in the table so that the line is read-only and cannot be written.

After that, transaction B requests a write lock for the entire table .

If transaction B is successful, it is theoretically possible to modify any row in the table, which is in conflict with a row lock held by a.

The database needs to avoid this conflict, which means that the request for B is blocked until a row lock is released.

How does a database judge this conflict?

Step1: Determine if the table has been locked by another transaction table
Step2: Determines whether each row in the table has been locked by a row lock.

Note that this method of judging step2 is not very efficient, as it is necessary to traverse the entire table.
So there is the intention to lock.

In the case of an intent lock, transaction a must first request an intent-shared lock on the table, and then apply for a row lock after the success.

In the case where the intent lock is present, the above judgment can be changed to

Step1: No change
Step2: The discovery of a shared lock on the table indicates that some rows in the table are locked by a shared row lock, so the write lock on the transaction B application table is blocked.

Note: The request for intent lock is done by the database, that is, when transaction a requests a row lock, the database will automatically start the application form intent lock, do not need our programmer to use code to apply.

---------------------------------------------------------------------------------------------------------------




Database row locks, table locks