• Old Liu is about to find a job of graduate students, self-taught big data development, along the way, feeling quite deep, online big data data of good and bad mixed, so want to write a detailed guide to big data development. This guide to the big data [basic knowledge] [framework analysis] [source understanding] are described in their own words, so that partners self-study from now on do not ask people.
  • Your praise is my motivation to continue to update, no white whoring, see will have harvest, refueling together.

Today to share with you is the basic part of big data development MySQL lock, lock in MySQL knowledge points belong to a more important part, we must have a good understanding of Old Liu’s words, MySQL lock outline is as follows:

After reading Lao Liu’s article, I hope you can grasp the following contents:

  1. MySQL lock classification
  2. Table – level lock table lock, metadata lock principle
  3. Principle of row lock, difference between record lock and gap lock, principle of deadlock and deadlock scenario

MySQL lock is introduced

Why is there a lock?

Multiple programs access MySQL tables or records, there will be race conditions, to solve this problem, we proposed the lock.

MySQL > lock ();

MySQL table level lock

Table level locking is implemented by MySQL SQL Layer. Once a table is locked, only that table can be operated on. It has two kinds: one is the table lock, the other is the metadata lock.

Table locks

Table locks can be expressed in two forms: Table Read Lock and Table Write Lock.

SQL > add table locks manually

Lock Table Table nameread(write), table name 2read(write), other;

Copy the code

SQL > delete table lock

unlock tables;

Copy the code

SQL > alter table lock;

show open tables;

Copy the code

Light said not practice false handle, old Liu with a case to tell you partners about table lock, we follow together to practice.

Table locks demo

- the new table

CREATE TABLE mylock (

id int(11) NOT NULL AUTO_INCREMENT,

NAME varchar(20) DEFAULT NULL,

PRIMARY KEY (id)

);

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');

Copy the code

Demonstrates table read locking

Alter table read lock (mylock)

session1: lock table mylock read;

Copy the code

2, session1 can still query the table after adding the table read lock

session1: select * from mylock;

Copy the code

3, Once this table is locked, only this table can be operated on, cannot access other tables, until this table is released. I used session1 to access the table TDEP and failed to access it, as shown in the figure below:

4. Although this table is locked, other sessions can access it.

Session2: select * from mylock;

Copy the code

Alter table mylock (*); alter table mylock (*);

Session2: update mylockset name='x' where id=2;

Copy the code

6, session1 release, session2 can be modified.

Session1: unlock tables;

Session2: Rows matched: 1 Changed: 1 Warnings: 0

Copy the code

Table read lock content is demonstrated, now to demonstrate the table write lock content.

1, write lock myLock

session1: lock table mylock write;

Copy the code

2, Session1 can access mylock table, but not other tables.

3, Session1 can also modify the table, but session2 can read and modify the table.

Session2 will be acquired only after session1 releases the write lock.

Session1: unlock tables;

Copy the code

Session2:4 rowsin set (41.65 sec)

Copy the code

Read lock is a shared read lock. You can use it yourself, and other people can access it. Write lock is a table exclusive write lock, meaning that only oneself can access, other people cannot access.

Metadata lock

What is metadata locking?

Metadata, or MDL, is an automatic read lock for a table that is being added, deleted, modified, or checked in MySQL 5.5. MDL write lock is automatically added when a table is being modified.

Note: this lock is automatically committed, and MDL read locks are automatically added when the transaction is started.

Now start the demonstration of metadata lock, we follow Liu practice together.

SQL > alter TABLE read lock (session1);

session1: begin;

          select * from mylock;

Copy the code

Session2 blocks when modifying this table.

session2: alter table mylock add f int;

Copy the code

Session1 commit transaction or ROLLBACK release read lock.

4. Session2 modification is complete.

MySQL row-level locks

MySQL row-level lock is implemented by the storage engine, using the storage engine to lock index items to achieve, mainly talking about InnoDB row-level lock.

InnoDB row-level locks are divided into three types according to the lock scope:

  • Record lock: Locks a record in an index.
  • Gap lock: A gap lock that either locks the values in the middle of the index record, before the first index record, or after the last index record.
  • Next-key locks: Combination of record locks and gap locks (without looking at it)

InnoDB row-level lock, by function:

  • Shared read lock (S) : Manually added to allow one transaction to read a row. Other transactions can read data but cannot modify data.

    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- SHARE read locks are manually added

    Copy the code

  • Exclusive write lock (X) : Automatic lock, which means that after a transaction has an exclusive lock on a row of data, other transactions cannot have another lock on it.

InnoDB also implements table-level locking, also known as intentional locking, which is used internally by mysql without user intervention.

Two-phase lock (2PL)

Two-stage locking means that the lock operation is divided into two stages: locking stage and unlocking stage.

Lock stage: only lock, not lock.

Unlock stage: lock only, no lock.

Row locking demo

InnoDB locks rows by locking index entries on indexes, so InnoDB uses row-level locks only for data retrieved by index criteria. Otherwise, InnoDB uses table locks!

Do read lock

Select * from row where id=1; select * from row where id=1;

session1: begin;

          select * from mylock where ID=1 lock in share mode; 

Copy the code

Session2 (id=1); session2 (id=1);

Session2: update mylockset name='M' where id=2;

Session2: update mylockset name='M' where id=1;

Copy the code

3, Session2 can be modified only after session1 is committed.

session1: commit; 

Session2: update mylockset name='M' where id=1; 

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Copy the code

With regard to row locking, note that using index plus row locks, unlocked rows can be accessed.

The row read lock is upgraded to a table lock

Select * from mylock; select * from mylock; select * from mylock; select * from mylock;

Session1 starts transaction

session1: begin;

Copy the code

Select * from ‘c’ where name=’c’

select * from mylock where name='c' lock in share mode;

Copy the code

Session2 update block, update table lock without index row lock

update mylock set name='N' where id=2;

Copy the code

Session1 commit transaction or ROLLBACK release read lock

commit;

Copy the code

Session2 will change successfully

update mylock set name='N' where id=2;

Copy the code

Do write lock

Session1 (id=1); session1 (id=1);

session1: begin;

          select * from mylock where id=1 for update;

Copy the code

2. Here’s a particularly important point that many people get wrong!

An exclusive lock locks a row of data so that no other transaction can read or modify the row.

An exclusive lock means that after a transaction has an exclusive lock on a row of data, other transactions cannot have another lock on it. MySQL InnoDB engine default to update data, delete data, insert data automatically add exclusive lock type, default to select data. For update statement, add shared lock, select… Lock in share mode statement. For update, lock in share mode, lock in share mode, select… The from… Query data because normal queries do not have any locking mechanism.

So session2 can access rows with ID =1.

session2: select * from mylock where id=1 ;

Copy the code

3. But you can’t keep it locked

session2: select * from mylock where id=1 lock in share mode ;

Copy the code

Session2 will execute successfully only after session1 commits the transaction or ROLLBACK releases the write lock

Clearance lock

According to the retrieval condition, the record value A closest to the retrieval condition is searched to the left as the left interval, and the record value B closest to the retrieval condition is searched to the right as the right interval, that is, the locked gap is (A, B). According to where number=5 in the figure, the interval range of gap lock is (4,11);

Gap locks prevent two things

  1. Prevents insertion of data in gaps
  2. Prevents existing data from being updated to data in the gap

Clearance condition:

  1. The ID and number are in the gap
  2. The ID and number are outside the gap
  3. Id is in the gap and number is out of the gap
  4. Id is outside the gap, number is inside the gap
  5. Id and number are edge data

Non-unique index equivalent

update news set number=3 where number=4;

Copy the code

(2, 4), (4, 5), that is, the gap between records (id=1,number=2) and records (id=3,number=4) will be locked. The gap between record (id=3,number=4) and record (id=6,number=5) is locked.

When we add the data, the result is as follows:

Insert into news value (2, 3);

All in the gap, blocked

Insert into news value (7, 8);

All outside the gap. Success

Insert into news value (2, 8);

Id in the gap, number out of the gap, success

Insert into news value (4, 8);

Id in the gap, number out of the gap, success

Insert into news value (7, 3);

Id outside the gap, number inside the gap, blocked

Insert into news value (7, 2);

Id is outside the gap, number is upper edge data, block

Insert into news value (2, 2);

Id is in the gap, number is upper edge data, block

Insert into news value (7, 5);

Id is outside the gap. Number is the lower edge data. Success

Insert into news value (4, 5);

Id is in the gap, number is the lower edge data, blocked

Copy the code

We can get that as long as the number (after where) is in the gap (2, 3, 4) and does not include the last number (5) it will block regardless of the ID. If it’s the lower edge data, you need to see if the ID is in the gap.

Primary key index range

Since the primary key cannot be repeated, the ID has no edge data.

update news set number=3 where id>1 and id <6;

Copy the code
Insert into news value (2, 3);

All in the gap, blocked

Insert into news value (7, 8);

All outside the gap. Success

Insert into news value (2, 8);

Id inside the gap, number outside the gap, blocked

Insert into news value (4, 8);

Id inside the gap, number outside the gap, blocked

Insert into news value (7, 3);

Id is outside the gap, number is inside the gap. Success

Copy the code

We can get that as long as the ID (after where) is in the gap (2, 4, 5), it will block regardless of the number.

Non-unique index infinity

update news set number=3 where number=13 ;

Copy the code
Insert into news value (11, 5);

Execute successfully

Insert into news value (12, 11);

Execute successfully

Insert into news value (14, 11);

blocking

Insert into news value (15, 12);

blocking

Copy the code

If number=13, the nearest value 11 is obtained as the left interval; if there is no record, infinity is obtained as the right interval. Therefore, the gap lock range of session 1 (11, infinity) will be blocked only when both ID and number are met.

A deadlock

Two sessions wait for each other’s resources to be released before releasing their own resources, resulting in deadlocks, mainly due to sequence problems.

Session1: lock id=1; session2: lock id=2; session1: lock id=2;

How do I resolve deadlocks?

By default, MySQL proactively detects deadlocks and rolls back one of the least affected transactions, waiting for another transaction to complete, and then re-executing that transaction.

conclusion

This article, as the third article in MySQL’s big data development guide, introduces the contents of MySQL lock in detail. I hope you will follow Liu’s article and try to explain these points in your own words.

Although the current level may not be as good as you, but Old Liu will strive to become more excellent, let you friends self-study from now on!

Big Data Development Guide address is as follows:

  • github：https://github.com/BigDataLaoLiu/BigDataGuide
  • Yards cloud: https://gitee.com/BigDataLiu/BigDataGuide

If you have any questions, please contact our official account: hardworking Old Liu. The article has seen this, like attention to support a wave!