The concept of database locks and the use of display locks

1 Overview

"Why do you want to lock?"

When concurrent transactions access a resource at the same time, it is possible to cause inconsistent data, so a mechanism is needed to order data access to ensure consistency of database data. A lock is one of the mechanisms.

A database is a shared resource used by multiple users, such as a user table t_user, and the person in front of two browsers logs in to the same account and changes the phone number. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data, destroy the consistency of the database (dirty read, non-repeatable read, Phantom reading, etc.), may produce deadlock. In order to solve this problem, locking is a very important technology, and it is a good scheme to implement the concurrency control of the database. Simply put, when a transaction that executes an SQL statement wants to manipulate the table record, make a request to the database, lock the recordset you access, and the other transaction cannot update the data until the transaction releases the lock.

The task of concurrency control in a database management system (DBMS) is to ensure that multiple transactions concurrently access the same data in the database without disrupting the isolation and uniformity of the transaction and the consistency of the database. The following is an example of the data inconsistency problem caused by concurrent operations:

There are two tickets available at the ticket counter, while the ticket balance in the database for a train ticket is X. Two ticketing points sell a ticket at the same time, while the change balance is X-1 write back to the database, which results in the actual sale of two train tickets and the database records are only one missing. This occurs because two transactions are read into the same data and modified at the same time, and the result of one transaction commit destroys the result of another transaction commit, causing the modification of its data to be lost, which destroys the isolation of the transaction. Concurrency control is the kind of problem to solve.

Blocking, timestamp, optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main techniques used in concurrency control.

2 Concept Introduction

Lock type:

Read lock: Shared lock, can be shared by multiple read operations, can only be shared to read.

Write lock: Exclusive lock;

Size of Lock:

Table Lock: Locks are applied at the table level, concurrency is low, and table-level locks are more granular than row-level locks

Row locks: Locks are applied at the row level, concurrency is high, and the cost of maintaining the lock state is large;

The smaller the size of the lock, the more likely it is to cause a deadlock condition. If two lines need to be edited at the same time, each other needs to wait for another line to unlock, causing the deadlock situation.

Lock strategy: To seek a balance between lock granularity and data security, so that the concurrency mechanism is normal while maintaining a normal lock state.

Storage Engine-level locks: the level and when the lock is applied or released is at the discretion of the storage engine; determined by the engine

MySQL Server: Table level, at its discretion, allows explicit requests, and allows users to request and apply locks manually, at the table level.

Lock Category:

Explicit Lock: The user manually requested the lock;

Implicit lock: A lock that the storage engine imposes on its own needs;

3 use of an explicit lock

(1) LOCK TABLES

LOCK TABLES tbl_name read|write, Tbl_name read|write, ...

UNLOCK TABLES #解开全部的锁, not followed by table name

Write locks are applied, write locks are exclusive, no other threads are allowed to read and write, and self-imposed locks are unaffected

Lock tables Classlist write;

MariaDB [sunny]> Lock tables Classlist write;

Query OK, 0 rows Affected (0.00 sec)

MariaDB [sunny]> SELECT * from Classlist; #此时是可以正常读写

Empty Set (0.00 sec)

Note that at this point when another user connects (logging in to the database from another terminal), the results do not come out and are locked in the state when viewed.

MariaDB [sunny]> SELECT * from Classlist;

After the read lock is applied, other processes can be seen, but cannot insert data, read locks are rejected for write locks, and can be unlocked before normal

MariaDB [sunny]> lock tables Classlist read;

Query OK, 0 rows Affected (0.00 sec)

At this point, when you insert data from another terminal, you are in a locked state

MariaDB [sunny]> INSERT INTO classlist values ("Sunny", 1, "100");

(2) FLUSH TABLES: Synchronizes the in-memory data to the disk, that is, the brush write operation, but this synchronization process can apply a lock, once the lock is applied, that is, the corresponding table is executed to synchronize, close, open, and apply the lock. Once a lock is applied, the read operation of other threads is not affected at this time, but the write operation cannot be executed and must be unlocked before it takes effect.

FLUSH TABLES tbl_name,... [with READ LOCK];

UNLOCK TABLES;

Lock all the tables, note that they can be locked for a table

MariaDB [sunny]> flush tables with read lock;

Query OK, 0 rows Affected (0.00 sec)

Other threads, unlocked before data can be inserted

MariaDB [sunny]> INSERT INTO classlist values ("Tracy", 2, "99");

Query OK, 1 row affected (1 min 5.58 sec)

(3) SELECT cluase #FOR update request to apply a write lock, lock in SHARE mode to apply read lock, generally do not operate

[For UPDATE | LOCK in SHARE MODE]

The general operation is to manually unlock. However, it may affect operations already in the software layer.

For more information about the recommended links:

Lock mechanism of database: http://blog.csdn.net/lexang1/article/details/52248686

Why does a database need a lock mechanism?: https://www.cnblogs.com/fanp/p/4633453.html

The concept of database locks and the use of display locks