Related blog recommendations:

• Mysql high performance index

Mysql locks and transactions

The usual business, at most, that is, to write a simple SQL, even with less transaction, lack understanding of the lock that is even more, a great god before share transaction isolation level for the mysql, feel very interesting, just found a great post, and then also collected some relevant knowledge, to learn, Mysql locks and transactions in the mysterious veil, the main content includes

1. The difference between shared lock and exclusive lock and the applicable scope
2. Mysql > alter table lock
3. How to determine whether a SQL query performs a table lock or a row lock
4. What is a transaction and how does it work
5. The properties of transactions are ACID
6. Transaction isolation levels (RU, RC, RR, SER)
7. How do I check the isolation level used by mysql

I. the lock

In learning multithreading, we also often encounter the lock this thing, at that time talk about more is optimistic lock and pessimistic lock, the two kinds of lock and DB often said shared lock and exclusive lock what is the difference? Let’s start with the definitions we know about optimistic and pessimistic locks

• Optimistic lock: The CAS in multithreading is an optimistic lock, which is actually not locked, but is attempted first and retried if it fails (or processed according to the failure policy)
• Pessimistic lock: lock, only one thread can access at a time, the rest must wait

1. Shared locks and exclusive locks

A. a Shared lock

As the name implies, this lock can be shared by multiple people. It can also be called read lock (S lock).

In DB, read lock means that all partners who read data will not be blocked by the lock, and they can safely and boldly obtain data. In a professional way, multiple connections are allowed to read the same resource concurrently at the same time

B. exclusive lock

An exclusive lock means that when one person holds the lock, other people will fail to compete for the lock and have to wait for the lock to be released. This is also called write lock (X lock).

In DB, write lock means that only one partner can operate at the same time, and all others, whether read or write, must queue. In professional terms, write lock blocks other read or write lock requests, ensuring that only one connection can write resources at the same time, and preventing other connections from reading or writing resources

C. GapLock and Next Key Lock

• The next key lock is used to lock a range in range matching scenarios
• GapLock is primarily used to lock boundaries

Case as follows (note, columnA non-unique index, RR isolation level)

• where columnA between 10 and 30The next key lock ensures that no new rows are inserted within 10, 30
• where columnA = 10, gap lock ensures that a row with columnA=10 is not inserted again

2. Table and row locks

For DB operations, there are usually two cases, one is to lock the table, one is to lock the row

• Table lock: the write lock is occupied by one connection in the entire table. As a result, the read lock or write lock of other connections is blocked. The entire table reads and writes are affected
• Row lock: indicates that some rows in the table are locked by a connection, but other rows can still be read or write locked by other connections. Only the rows that are locked are affected

The question then arises, what SQL causes row locks and what causes write locks? How can we even tell if a SQL is asking for a lock, whether it is a read lock or a write lock?

3. How to use the lock

The previous section raised the question, so now it’s time to look at how to use and analyze locks

• select
• update
• delete
• insert

It is easy to conclude that update, DELETE, and INSERT involve write locks; And the vast majority of such operations are performed on specific rows (think about why?). , so row locking is more common

The SELECT read operation is a bit special

A. select analysis

Multiple-version-concurrency – Control (MVCC) is a variant of row-level locking. It avoids locking in normal read situations, so the overhead is lower. The bottom one has no read lock and no write lock

Snapshot read, unlocked

select * from table.Copy the code

Currently read, the SELECT statement can specify read and write locks as follows

- read lock
select * from table lock in share mode;

- write lock
select * from table for update;
Copy the code

Insert, update, and delete are also currently read for the following reasons:

1. Update and DELETE operation process decomposition:

• The first record that meets the WHERE condition is first queried and locked
• Update this record and read the next record
• Update the record and continue reading until the end

2. Insert operation process decomposition:

• Unique key conflict detection, there will be a current read
• Insert when there is no conflict

B. SQL instance analysis

- SQL1:
select * from t1 where id = 10;

- SQL2:
delete from t1 where id = 10;
Copy the code

Before analyzing the above SQL, you need to clarify a few premises:

• Whether id is a primary key (whether ID has an index)
• The isolation level of the system (see what the isolation level is below)

Description:

Case1: primary key +RC level

• Sql1 is not locked, MySQL is using multi-version concurrency control, read is not locked
• Sql2 add write lock (X lock), only lock id=10 row

Case2: unique index + RC level

• Select * from [id= ‘d’,id=10]; select * from [name= ‘d’,id=10]; select * from [name= ‘d’,id=10]

Case3: id non-unique index +RC

• Sql2 with write locks, as shown in the following case, will have four write locks

Case4: No index +RC

• Sql2 analysis: If there is no index on the ID column, SQL will filter through the full scan of the clustered index, because filtering is performed at MySQL Server level. So every record, whether or not it meets the criteria, is locked with a write lock (X lock).
• However, for the sake of efficiency, MySQL has made optimization. For records that do not meet the condition, it will release the lock after judgment. Finally, it holds the lock on the record that meets the condition, but the lock on the record that does not meet the condition will not be saved

Case5: primary key + RR

Lock with case1

Case6: unique index +RR

Case2 lock

Case7: non-unique index +RR

The RR level does not allow phantom reads. To put it simply, records that meet the conditions cannot be modified when adding or during the locking process

In the figure below, in addition to the similar X lock shown in Figure 3, a gap lock will be added, which mainly ensures that no new records can be inserted into those positions

Case8: no index +RR

• In Repeatable Read isolation level, if the current Read of the full table scan is performed, all records in the table and all gaps in the cluster index are locked, preventing all concurrent update/delete/insert operations

Level of case9: Serializable

• Sql2: Serializable isolation level. For SQL2: delete from t1 where id = 10; The Serializable isolation level is exactly the same as the Repeatable Read isolation level
• SQL1: In the RC and RR isolation levels, snapshot reads are not locked. However, at the Serializable isolation level, SQL1 adds read locks, meaning that snapshot reads no longer exist and MVCC concurrency control is downgraded to lock-based CC

---

II. The transaction

Transaction is a very important knowledge in DB, then our goal is to understand what is a transaction, how to use transactions, and what is the relationship between transactions and locks

Note: The analysis of this paper is mainly based on mysql’s InnorDB storage engine as the standard

Definition 1.

A transaction is a set of atomic SQL, or a single unit of work.

A transaction means that either the mysql engine will execute all of the SQL statements, or none of them (for example, if one of the statements fails).

2. ACID properties

A: Atomiciy

A transaction must ensure that all operations are either executed or rolled back, and there is no possibility that only part of the operation is executed.

B. C:consistency

Data must be guaranteed to transition from one consistent state to another consistent state.

C. I:isolation

When a transaction is not completed, it is usually guaranteed that no other Session will see the result of the transaction

D. d: Durability

Once a transaction is committed, the data is saved and will not be lost even if the system crashes after the commit

3. Isolation level

The isolation levels mentioned in the SQL analysis of locks are RU, RC, RR, and Serializable

Before WE get to that, let’s get to a couple of concepts

A. Basic concepts

• Dirty read: read data that has not been committed by a transaction. Because the transaction cannot guarantee success, the read data cannot guarantee accuracy
• Non-repeatable reads: Simply put, the data read in a transaction may change. The same SQL may be executed multiple times in a transaction, resulting in different results
• Phantom read: a query is executed in session T1, and a new row is inserted in session T2, which meets the criteria for the query used by T1. T1 then retrieves the table again using the same query, but sees the new row that transaction T2 just inserted
• Lock to read:select * from table ...SQL > select * from SQL > lock;

B. RU: Read Uncommited Not committed

Changes in a transaction, even if they are not committed, are visible to other sessions, indicating the possibility of dirty reads, which is not the case in most databases

C. RC: Read Commited Indicates a Read

This isolation level ensures that if a transaction is not completely successful (commit completion), the operations in the transaction are not visible to other sessions, avoiding the possibility of dirty reads

However, non-repeatability may occur, for example:

• Session T1, perform the queryselect * from where id=1, returns a result the first time
• Session T2, perform the modificationupdate table set updated=xxx where id=1And submit
• Session T1, perform the query againselect * from where id=1The update field is not the same as the previous one

In the actual production environment, this level is often used, specifically check the company’s DB isolation level is this

An RC level demo procedure:

• Session 1, start transaction, query
• Session 2, start transaction, update DB, commit transaction
• Session 1, query again, commit transaction
• As you can see from the following example, the same SQL is executed in session 1 with different results

The related SQL code is as follows:

-- Sets the session isolation level
set session transaction ioslation read commited;

-- View the current session isolation level
select @@tx_isolation;

-- Operation for session 1
start transaction;
select * from newuser where userId=1;


Session 2 starts operation
start transaction;
select * from newuser where userId=1;
update newuser set updated=1521786092 where userId=1;
select * from newuser where userId=1;
commit;


Enter session 1 again, execute the same SQL as last time, and compare the output twice
select * from newuser where userId=1;

-- Note that the updated SQL field is updated in session 1
-- The normal situation is as shown in the demo diagram above, which will change


-- Close the session
commit;

SQL > alter database
select * from newuser where userId=1;
Copy the code

D. RR: Repeatable Read Repeatability

The unified read SQL is executed multiple times in a transaction and returns the same result. This isolation level solves the dirty read problem, the phantom read problem

Example of how to resolve dirty reads (repeat the above procedure)

• Find that the same result is returned regardless of session 1’s SQL

E. Serializable

The strongest isolation level, by locking each row read in a transaction, writes and writes locks, guarantees no phantom read problems, but can lead to numerous timeouts and lock contention issues.

F. Common commands

• To view the current session isolation level:select @@tx_isolation
• To view the current system isolation level:select @@global.tx_isolation
• To set the current session isolation level:set session transaction isolation level read committed;
• Set the current isolation level of the system:set global transaction isolation level read committed;
• The command line,
  • Start transaction:start transactioin;
  • Commit:commit;

4. Use posture

In the previous demonstration of transaction isolation levels, the example presented demonstrates the transaction usage posture, generally as three steps:

• Start the transactionstart transaction;
• Execute your business SQL
• Commit the transactioncommit;

We now demonstrate the effect of read and write locks on another transaction in the following transaction

A. Impact of read locks

We use mysql’s default RR level for testing, userId as primary key

1 - session
start transaction;
select * from newuser where userId=1 lock in share mode;

Enter session 2
start transaction;
select * from newuser where userId=1; - will be output
select * from newuser where userId=1 lock in share mode; - will be output
update newuser set updated=1521787137 where userId=1; - suspends


Enter session 1
Commit, at this point to see if the write of session 2 is complete
commit;

Enter session 2
commit;
Copy the code

Actual execution demo:

B. Impact of write locks

1 - session
start transaction;
select * from newuser where userId=1 for update;

Enter session 2
start transaction;
select * from newuser where userId=1; - will be output
select * from newuser where userId=1 lock in share mode; - will live

-- update newuser set updated=1521787137 where userId=1; - will live

Enter session 1
Commit, at this point to see if the write of session 2 is complete
commit;

Enter session 2
commit;
Copy the code

Actual execution demo:

C. summary

• Read locks block other SQL requests to write locks
• Write locks block the SQL execution of other read locks and write locks
• The lock is released only after the transaction commits
• Note also that the above transaction does not release the lock until after it commits, so deadlocks may occur if two transaction cycles depend on the lock

---

III. The summary

Locks and transactions are very important knowledge points in DB, in our actual coding process (generally for mysql, InnorDB storage engine, RR isolation level), make the following summary

1. Analysis of SQL

• select * from table where xxx;(Read snapshots, generally unlocked)
• select * from table where xxx lock in share mode;(Read lock, block other write lock requests, but other read lock requests are not affected)
• select * from table where xxx for update;(Write lock, block other read/write requests)
• update tableName set xxx(write)
• insert(write)
• delete(write)

2. The transaction

In simple terms, a transaction is a set of SQL that either all execute successfully or all fail

Four characteristics: A(atomicity)C(consistency)I(isolation)D (persistence)

4 isolation levels :(mysql defaults to RR level)

Isolation level	Dirty read	Unrepeatable read	Phantom read	Read lock
read uncommited	may	may	may	There is no
read commited	Can’t be	may	may	There is no
repeatable read	Can’t be	Can’t be	Can’t be	There is no
serializable	Can’t be	Can’t be	Can’t be	There are

Use posture:

start transaction;

-- XXX Specific SQL

commit;
Copy the code

IV. The other

reference

• Deep understanding of Mysql — locks, transactions, and concurrency control
• MySQL lock processing analysis

Personal Blog:A gray Blog

Hexo + Github Pages, hexo + Github Pages, hexo + Github Pages, Hexo + Github Pages

The statement

As far as letter book is inferior, has on the content, pure one’s opinion, because my ability is general, knowledge is limited, if discover bug or have better suggestion, welcome the criticism to point out at any time

• Micro Blog address: Small Gray Blog
• QQ: a gray /3302797840

Scanning attention