Shulou(Shulou.com)05/31 Report--

This article shows you what lock and transaction isolation levels are in MySQL. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something from the detailed introduction of this article.

1. Overview

(1)Definition of Lock

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads.

In a database, in addition to the traditional contention for computing resources (such as CPU, RAM, IO, etc.), data is also a resource that needs to be shared by users. How to ensure the consistency and validity of concurrent access is a problem that all databases must solve. Lock conflict is also an important factor affecting the performance of concurrent access. From this perspective, locks are especially important and complex for databases.

(2)Classification of locks

From the performance is divided into optimistic locks and pessimistic locks

Optimistic lock: Every time you get data, you think that others will not modify it, so you will not lock it, but when you update it, you will judge whether others have updated this data during this period.

Examples: ① Add a version field to the database table to record the version number of each modified data to prevent concurrent modification of data errors;② CAS atomic class.

Pessimism lock: Every time you get data, you think others will modify it, so you lock it every time you get data, so others will block it until they get the lock.

Example: synchronized keyword.

From the types of database operations are divided into read locks and write locks (all belong to pessimistic locks)

Read lock (shared lock): Multiple reads can be performed simultaneously on the same piece of data without affecting each other.

Write lock (exclusive lock): It blocks the write lock and read lock of other sessions until the current write operation is completed. (session: database connection)

From the granularity of data operation, it is divided into table lock and row lock.

2. Table lock and row lock

(1)Table lock (partial reading)

Table locks are biased towards MyISAM storage engine, with low overhead, fast locking, deadlock-free, large locking granularity, highest probability of lock conflict, and lowest concurrency.

1)basic operation

SQL table creation

CREATE TABLE `mylock` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'main key id',

`name` varchar(20) DEFAULT NULL COMMENT 'name',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert data

insert into `mylock` (`id`, `name`) values('1','a');

insert into `mylock` (`id`, `name`) values('2','b');

insert into `mylock` (`id`, `name`) values('3','c');

insert into `mylock` (`id`, `name`) values('4','d');

Manually add a watch lock

lock table table name read(write), table name 2 read(write)

Look at the locks on the table

show open tables

Delete table lock

unlock tables

2)Case Study (Read Lock)

The table can be read by the current session and other sessions.

Inserting or updating locked tables in the current session will report errors, and other session inserts or updates will wait.

3)Case Study (Write Lock)

The current session has no problem adding, deleting, or querying the table, while other sessions have blocked indexing operations on the table.

4)Case conclusion

MyISAM automatically adds read locks to all tables involved before executing query statements, and automatically adds write locks to all tables involved before executing add, delete and modify operations.

Read operations (read locks) on MyISAM tables do not block read requests from other processes on the same table, but block write requests on the same table. Only when the read lock is released will other processes 'writes be executed.

② Write operations (write locks) to MyISAM tables block other processes from reading and writing to the same table. Only when the write locks are released will other processes perform read and write operations.

Summary: Read locks block writes, but not reads. Write locks block reads and writes.

(2)Line lock (partial writing)

Row locks are biased towards InnoDB storage engines, with high overhead, slow locking, deadlocks, the smallest locking granularity, the lowest probability of lock conflicts, and the highest concurrency. InnoDB and MYISAM differ in two major ways: (1) transaction support; and (2) row-level locking.

1)row lock support transaction

Transaction and its ACID attribute

A transaction is a logical unit of processing consisting of a set of SQL statements that has the following four attributes, often referred to simply as the ACID attribute of the transaction.

Atomicity: A transaction is an atomic unit of operation that performs all or none of its modifications to data.

Consistency: The data must remain consistent at the beginning and completion of the transaction. This means that all relevant data rules must be applied to the modifications of the transaction to preserve data integrity; all internal data structures (such as B-tree indexes or doubly linked lists) must also be correct at the end of the transaction.

Isolation: The database system provides some isolation mechanism to ensure that transactions execute in a "separate" environment that is not affected by external concurrent operations. This means that intermediate states in a transaction are invisible to the outside world, and vice versa.

Durability: After a transaction is completed, its modifications to the data are permanent and can be maintained even in the event of a system failure.

Problems with concurrent transaction processing

(Note: It doesn't matter if you don't understand the following theoretical explanations, then look down, there are examples)

Lost Update: When two or more transactions select the same row and then update the row based on the initially selected value, the Lost Update problem occurs because each transaction is unaware of the existence of the other transactions-the last update overrides updates made by the other transactions. (It can be solved with a solution similar to optimistic locking)

Dirty Reads: A transaction is modifying a record, and the data of this record is in an inconsistent state before the transaction is completed and committed; at this time, another transaction reads the same record, and if it is not controlled, the second transaction reads the "dirty" data and performs further processing according to it, resulting in uncommitted data dependencies. This phenomenon is figuratively called "dirty reading."

Bottom line: Transaction A reads data that transaction B has modified but not yet committed, and operates on that data. At this point, if B's transaction rolls back, A reads invalid data that does not meet consistency requirements.

Non-Repeatable Reads: A transaction reads some data at a certain time after reading it, only to find that the data it reads has changed or some records have been deleted! This phenomenon is called "non-repeatable reading."

Bottom line: Transaction A reads modified data that transaction B has committed, which does not conform to isolation.

Phantom Reads: A transaction rereads previously retrieved data according to the same query criteria, but finds that other transactions insert new data that meets its query criteria. This phenomenon is called "Phantom Reads."

Bottom line: Transaction A reads new data that transaction B has committed, which is not isolation compliant.

Isolation level of transactions

"Dirty read,""non-repeatable read" and "phantom read" are actually database read consistency problems, which must be solved by a certain transaction isolation mechanism provided by the database.

The stricter the transaction isolation level of the database, the smaller the concurrency side effects, but the greater the performance cost, because transaction isolation essentially means that transactions are "serialized" to some extent, which is obviously contradictory to "concurrency."

View transaction isolation levels for current data: show variables like 'tx_isolation'

Set transaction isolation level: set tx_isolation='REPEATABLE-READ'

MySQL's default isolation level is repeatable.

2)Case Study of Line Lock

As shown below, a transaction needs to be opened, session_1 updates a row, session_2 updates a row at the same time will be blocked, but updates to other rows are normal

3)Isolation Level Case Study

CREATE TABLE `account` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'main key id',

`name` varchar(255) DEFAULT NULL COMMENT 'name',

`balance` int(11) DEFAULT NULL COMMENT 'Amounts',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

insert into `account` (`id`, `name`, `balance`) values('1','lilei','450');

insert into `account` (`id`, `name`, `balance`) values('2','hanmei','16000');

insert into `account` (`id`, `name`, `balance`) values('3','lucy','2400');

1. Reading not submitted:

a. Open a client A, set the isolation level of the current transaction to read uncommitted, set tx_isolation='read-uncommitted', and query the initial value of the account table.

b. Before client A commits, open another client B, set the isolation level of the current transaction to read uncommitted, and update the account table.

c. At this time, although the transaction of client B has not been submitted, client A can query the updated data of client B.

d. Once client B's transaction rolls back for some reason, all operations will be undone, and the data queried by client A is actually dirty data.

e. Dirty data presentation: In client A, execute update account set balance=balance-50 where id=1, the balance of lilei does not change to 350, but 400, the data consistency, because in this process, do not know that client B session rolled back, the row data is actually 450, 450-50=400, you can use the isolation level of read committed.

2 Read submitted:

a. Open a client A and set the isolation level of the current transaction to read committed, set tx_isolation='read-committed', query the initial value of the account table.

b. Before client A commits, open another client B, set the isolation level of the current transaction to read committed, and update the account table.

c. At this time, client B's transaction has not been submitted, client A can not query B has been updated data, to solve the dirty read problem.

d. Transaction commit for client B.

e. Client A executes the same query as in the previous step, and the result is inconsistent with the previous step, that is, the problem of non-repeatable reading is generated.

③ Repeatable reading

a. Open a client A, and set the isolation level of the current transaction to repeatable read, set tx_isolation='repeatable-read', query the initial value of the account table:

b. Before the transaction of client A commits, open another client B, and set the current transaction isolation level to repeatable read, update the account table:

c. Query all records in the account table at client A, and the query result is consistent with that in step a, and there is no problem of non-repeatable reading.

d. At client A, update account set balance=balance-50 where id=1, balance does not become 350-50=300, and the balance value of lilei is calculated using 300 in step b, so it is 250, and the consistency of the data is not destroyed. The MVCC mechanism is used in the isolation level of repeatable reading. Select operation does not update the version number, it is snapshot read (historical version);insert, update and delete update the version number, it is current read (current version).

e. Re-open client B, insert a new piece of data and submit:

f. Query all records in the account table on client A, and no new data is found, so there is no phantom reading:

g. Verify magic reading: update account set balance=666 where id=4 on client A; update succeeds, and query again to find the new data on client B:

④ Serialization

a. Open a client A and set the isolation level of the current transaction to serializable, set tx_isolation='serializable' and query the initial value of the account table:

b. Open a client B, and set the current transaction isolation level to serializable, insert a record error, the table is locked and the insertion fails. In MySQL, when the transaction isolation level is serializable, the table will be locked, so there will be no phantom reading. However, the concurrency performance of this isolation level is extremely low, and it is rarely used in development.

Question: MySQL default level is repeatable-read, is there a way to solve the illusion reading problem?

Gap lock can solve the problem of phantom reading in some cases. To avoid phantom reading, update account set name ='Zeki ' where id>3 and id can be executed under session_1 with gap lock

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.