当前位置：网站首页>Introduction of database lock, shared with InnoDB, exclusive lock

Introduction of database lock, shared with InnoDB, exclusive lock

2022-07-19 11:15:00 【Soup key】

Catalog
Introduction to lock
Locking mechanism ：
By operation
By granularity
By usage
Locks supported by different storage engines
InnoDB Shared lock
Shared lock features
Be careful
Create shared lock format
Examples demonstrate
InnoDB Exclusive lock
Exclusive lock features
Create exclusive lock format
Examples demonstrate
Introduction to lock
Locking mechanism ：
Database in order to ensure the consistency of data , A rule designed to be secure when shared resources are accessed concurrently
Locking mechanism is similar to synchronization in multithreading
The function is to ensure the consistency and security of data
By operation
Shared lock ：
It's also called read lock
For the same data , Multiple transaction read operations can be locked at the same time without affecting each other
But you can't modify the data
Exclusive lock ：
Also called write locks
Before the current operation is completed , It will block the reading and writing of other operations
By granularity
Table lock ：
Will lock the entire table
Low overhead , Locked fast
Large locking size , High probability of lock conflict , Low concurrency
There will be no deadlock
Row-level locks ：
Will lock the current row
Spending big , Lock the slow
The locking granularity is small , The probability of lock conflict is low , High concurrency
There will be deadlock
By usage
Pessimistic locking ：
Every time you query data, you think others will modify it , Very pessimistic , So lock when querying
Optimism lock ：
Every time you query data, you think others won't modify it , Very optimistic. , But when updating, it will determine whether others have updated this data during this period
Locks supported by different storage engines
InnoDB
Support table lock and row lock
MyISAM and memory
Only table locks are supported , Row locks are not supported
InnoDB Shared lock
Shared lock features
Data can be queried by multiple transactions , But it can't be modified
Shared locks and shared locks are mutually compatible
Be careful
InnoDB If the storage engine does not use indexed column locking , It's a watch lock
InnoDB By default, the storage engine adds row locks
Create shared lock format
select sentence lock in share mode;
Examples demonstrate
Data preparation

--  establish student surface 
CREATE TABLE student(
		id INT PRIMARY KEY auto_increment,
		name VARCHAR(20),
		age INT,
		score INT
);
--  Add data 
INSERT INTO student VALUES (NULL,' Zhang San ',23,99),(NULL,' Li Si ',24,95),(NULL,' Wang Wu ',25,98),(NULL,' Zhao Liu ',26,97);

open 2 Two windows open transactions respectively
At the window 1 Inquire about id And add a shared lock
At the window 2 The query again id Also no problem
( Data can be queried by multiple transactions )
In the window 2 Inquire about id It's no problem to add a shared lock
( Shared locks and shared locks are mutually compatible )
In the window 2 according to id Change the name
All the way around , Modification failed
( Shared lock data cannot be modified )
If you have to modify it, then in the window 1 Commit transaction
In the window 2 Make modifications to succeed
window 2 The transaction can be submitted in the window 1 See change , Is the real persistent state
After submission , Again in 2 A window opens the transaction
And in the window 1 Inquire about id Then add a shared lock
In the window 2 Modify relevant data , According to successful
This is because InnoDB By default, the storage engine adds row locks
And then 2 Windows commit transactions separately , After the transaction is committed, the lock does not exist
After submission , Again in 2 A window opens the transaction
And in the window 1 Inquire about score Rear lock
In the window 2 Modify relevant data , Modification failed
This is because InnoDB If the storage engine does not use indexed column locking , It's a watch lock
The last modification was successful because the lock was locked with id Query for conditions
and id It's the primary key , So it will have a primary key index
What is added is row lock
InnoDB Exclusive lock
Exclusive lock features
Locked data , It cannot be queried or modified by other transactions
Create exclusive lock format
select sentence for update;
Examples demonstrate
At the window 1 Inquire about id by 1 The data of , And add an exclusive lock
In the window 2 Inquire about id by 1 The data of , success
Verified that there is no problem with ordinary queries
Now it's still a query id by 1 The data of , But the shared lock is added , Turn around , Failure
Exclusive and shared locks are incompatible
take 2 After all windows commit transactions
And then 2 Two windows open transactions respectively
In the window 1 Inquire about id by 1 The data of , And add an exclusive lock
In the window 2 Inquire about id by 1 The data of , And add an exclusive lock
Turn around , Failure
Exclusive locks and exclusive locks are incompatible
take 2 After all windows commit transactions
And then 2 Two windows open transactions respectively
In the window 1 Inquire about id by 1 The data of , And add an exclusive lock
At the window 2 modify id by 1 The data of
Turn around , Failure
It can't be modified
Only the window 1 After committing the transaction , To modify it successfully