-mysql-Lock 2

Statement: The material used in this column is written by the VIP students of the Kay Academy, who has the right to be anonymous and has the final right to interpret the article; The Edith Academy is designed to promote students to learn from each other on the basis of public notes.

Table-Level Locks:

Before we introduced the row-level lock, as the name implies row-level lock is just a lock on one or more rows of data, because the row to lock, because a table will have a lot of rows of data, in this data to lock a few rows of data, it is more expensive resources. Table-level locks can lock the entire table, so the table-level lock has two modes: read-only mode and write-only mode, which is less resource-intensive than row-level, and is similar to read-only writing in the file's permissions. In general, the table lock is not often used, here is just a description of how to use table-level locks, and unlock table-level locks, and table-level lock data can be found on the network, so to understand, in the official MySQL also has the table lock syntax documents:

Https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

Table-level lock with read-only mode, syntax:

LOCK TABLES Table name READ
Example:

Let's open up a client and see if we can use the SELECT statement to query the data for this table-level lock table:

Since we use table-level locks in read-only mode, and naturally each user can read and query the data for this table, we can try to update the statement that will modify the table data to execute:

In row-level locks even if some rows of data are locked, you can still insert data using INSERT statements, so let's try to see if it works in a table lock:

It is known from the results that in the read-only mode of the table-level lock, no user is allowed to make any modifications to the locked form.

A natural DELETE statement cannot be used either:

So how do you unlock it? To see what happens when unlocking, the syntax for unlocking table-level locks is simple:
UNLOCK TABLES
Example:

Table-level locks using write-only mode, syntax:
LOCK TABLES Table name WRITE
Example:

In the write-only mode of table-level locks, only locked users can write data to the table, other users cannot write to the data, and other users can not even query data using the SELECT statement:

Locked users can insert data by using the INSERT statement, which is not allowed by other users:

The UPDATE statement is the same:

There are also DELETE statements:

If the user has a table-level lock on a table, the user can only manipulate the table before unlocking the table, and the other tables in the database are not allowed to do anything, and if the operation is an error:

The unlock syntax for write-only mode is the same, all unlock TABLES:

Summarize the table-level lock, table-level lock is to lock the table, relative to row-level lock is not so resource-intensive, table-level lock has two modes, read-only mode and write-only mode, read-only mode locked users and other users can only query data can not write data, write-only mode locked users to query data and write data, Other users can neither query data nor write data, and execute any SQL statement into a wait state until the table is unlocked, and the pending transaction is executed immediately when the table is unlocked. When a user uses a table-level lock on a table, they can only manipulate that table, and no other tables in the database are allowed to do anything.

Pessimistic Lock:

Pessimistic lock (Pessimistic lock) is a concept, to solve some problems of the pattern, not a specific mechanism, pessimistic lock, as its name, it refers to the external (including the system's current other transactions, as well as the transaction from the external system) changes conservative attitude (pessimistic), therefore, The data is locked during the entire processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data).

So the simple pessimistic lock is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that others want to take this data will block until it gets the lock. Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.

For example, before we do the train ticketing system of the small case, is the use of pessimistic lock, in our code are all with the help of the database with the lock mechanism to complete, when user A in the purchase of tickets, User B will not be able to buy tickets, or failed to purchase tickets, this is the entire data processing process, the information is locked, Have an obvious exclusivity (pessimism). Code:

The advantages and disadvantages of pessimistic lock:

Pessimistic concurrency control is actually a conservative strategy of "first fetch lock and then access", which guarantees the security of data processing. However, in terms of efficiency, the mechanism for handling locks can incur additional overhead for the database, as well as an increase in the chance of deadlocks, and in the case of read-only transactions where there is no conflict, there is no need to use locks, which can only increase the load on the system and reduce parallelism, if a transaction locks a row of data. Other transactions must wait for the transaction to finish before processing the data.

Optimistic Lock:

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, then let the return of the user error information, Let the user decide how to do it. In contrast to pessimistic locks, optimistic locks do not use the lock mechanism provided by the database when processing the database. The general way to implement optimistic locking is to record the data version.

A version of the data that is identified as an increase in data. When the data is read, the values of the version identity are read together, the data is updated every time, and the version identity is updated. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the version ID that was first taken out, if the current version number of the database table is equal to the version identity value of the first fetch, it is updated, otherwise it is considered to be outdated data.

So in fact, optimistic lock and pessimistic lock is also a concept, to solve certain business needs of the pattern, not a specific mechanism, optimistic lock is the main implementation of our developers themselves by adding a database to store the data version of the column, and then through the code to determine the version of the data, Rather than using a database-based lock to complete a pattern of locking data.

For example, we assume a situation: User A and User B at the same time in the ATM machine to an account balance of 2000 yuan account withdrawals, two users are queried to account and 2000 yuan, so user A to withdraw 1500, User B to withdraw 1000, and then the two withdrawal transactions will be submitted at the same time, If the bank's system logic is not good enough, there will be 2000-1500-1000=-500 results, the account balance will appear negative.
：

In the case of such withdrawals, if the pessimistic lock to lock the data, because of its exclusivity, then another user can not query the account balance, can only be in a wait state, because in the pessimistic lock before the end of the transaction data are locked, and in the bank in such a large amount of data, This row-level lock with shared locks also consumes resources. So we need to use optimistic lock, optimistic lock only when the operation is committed to lock the data. In the optimistic lock we can set a version number of the data, once this data changes, the version number will change, each operation will first determine whether the version number is the latest version number, not the words are not allowed to operate, in the implementation of the optimistic lock we do not use the database comes with the lock, So the user can be arbitrary query or submit operation.
：

Below we do a simple withdrawal system to demonstrate how to implement optimistic locking:
First, prepare a table to fill a row of data:

code example:

Operation Result:

Advantages and disadvantages of optimistic locking:

Optimistic concurrency control believes that the probability of data race between transactions is relatively small, so do it as directly as possible, until it is committed, so that no locks and deadlocks are generated. However, if it is straightforward to do so, it is still possible to encounter unexpected results, such as two transactions have read a row of the database, after modification to write back to the database, then encountered a problem.

Introduction to Dirty reading:

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 of this "learning Java, to the kaige123.com" data is not submitted data, then another transaction read this data is dirty data (Dirty data), the operation according to dirty information may be incorrect.

Non-repeatable READ:

Within a transaction, the same data is read multiple times. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data of the first transaction. Because of the modification of the second transaction, the data that the first transaction reads may be different, so that the data that is read two times within a transaction is not the same, so called non-repeatable read, that is, the original read is not repeatable.

Phantom read:

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, such as "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 there will be a user in the operation of the first transaction in the future. The data rows that are not modified in the table are seen as if there was a hallucination. The general solution to Phantom reading is to increase the range lock ranges, lock the search scope to read-only, thus avoiding phantom reading.

-mysql-Lock 2