This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Lock definition

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

In database systems, in addition to traditional computer resources (e.g., CPU, RAM). In addition to contention for I/O, data is also a resource that can be shared by multiple users. How to ensure concurrent data access, consistency and validity is a problem that all databases must solve. Lock conflict is also an important factor that affects the performance of database concurrent access. From this perspective, locks become more important and complex for databases.

Life shopping Case

For example, we go to Taobao to buy a commodity, the commodity inventory is only one, at this time, if there are other buyers. So how to solve the problem of whether you bought it or someone else bought it. Example:Transactions are definitely used here, so we take the quantity of the item from the inventory and then insert the order. Insert payment information after payment. The number of items is then updated, a process that uses locks to protect limited resources and resolve the conflict between isolation and concurrency.

The classification of the lock

Types of data operations (read/write)

  • Read lock (shared lock) : Multiple read operations can be performed simultaneously for the same data without affecting each other
  • Write lock (exclusive lock) : It blocks other write locks and read locks until the current write operation completes

The granularity of data operations

  • Table locks
  • Row locks

Three common locks

Table lock (offset read)

Features biased to MyISAM storage engine, low overhead, add lock block; Without a deadlock; The lock granularity is large, resulting in a high probability of lock conflict and the lowest concurrency. Case study to create a table SQL

use big_data;

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ' '
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;
Copy the code

Add read lock Add write lock Conclusion the case

Table lock analysis

  1. Query a locked table
show open tables;
Copy the code
  1. If table locks are analyzed

The table locking in the system was analyzed by checking the table_LOCKS_WAITED and table_locks_IMMEDIATE state variables.

SQL: show status like 'table%';
Copy the code

Example:Mysql > alter table lock (‘ lock ‘, ‘lock’);

  • Table_locks_immediate: indicates the number of times that the table level lock is generated. It indicates the number of times that the lock can be queried. Each time the lock value is increased
  • Table_locks_waited: waited number of lock contention caused by tag (fail to understand the number of lock acquisition, the lock value is increased by 1 each time waited), this value indicates that there is serious table-level contention.

In addition, Myisam’s read-write lock scheduling is write-first, which is why Myisam is not a good engine for writing to a master table, because after a lock is written, no other thread can do anything, and a large number of updates will make it difficult for a query to get a lock, resulting in permanent blocking.

Page locks

The overhead and locking time are between table locking and row locking. Deadlocks occur; The locking granularity is between table and row locks, and the concurrency is average.

The resources

  • mysql.com