This is the seventh day of my participation in the August More text Challenge. For details, see:August is more challenging

1. Table lock (partial read)

It is biased to MyISAM storage engine, with low overhead, fast locking, no deadlock, large locking granularity, high probability of lock conflict and the lowest concurrency.

Build table

create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)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');
Copy the code

Basic operations of locks

Check which tables have locksSHOW OPENTABLES; # add read LOCK to mylock table and write LOCK to book tableTABLEmylock READ,book WRITE; # UNLOCK TABLES;Copy the code

Add the effect after reading the lock

# add LOCK to myLockTABLE mylock READ;

SELECT * FROM mylock;

UPDATE mylock SET `name`="a2" WHERE id = 1; 

SELECT * FROM book;

UNLOCK TABLES;
Copy the code

Results:

Query the current lock table.

Failed to update the current lock table.

Description Failed to query other unlocked tables.

At this time, start a terminal to its locked table operation, can query, modify the operation will be blocked.

The lock is released. The other terminal acquires the lock. Successful execution.

The effect of adding a write lock

LOCK TABLE mylock WRITE;

SELECT * FROM mylock;

UPDATE mylock SET `name`="a2" WHERE id = 1; 

SELECT * FROM book;
Copy the code

Results:

You can query the current table.

You can modify the current table.

Other tables that are not locked cannot be queried.

Other terminals cannot query the currently locked table, and any operation will be blocked.

The lock is released. Another terminal acquires the lock. The execution succeeds.

A read lock blocks a write but not a read, while a write lock blocks both a read and a write.

2, table lock analysis

SHOW STATUS LIKE “table%”; Perform table lock analysis

SHOW STATUS LIKE “table%”; Two parameters of

  • Table_locks_immediate: Indicates the number of times a table-level lock is generated. It indicates the number of times a lock can be obtained immediately. Each time the lock value is obtained immediately, the value is increased by 1.
  • Table_locks_waited: number of waits due to table-level lock contention. Number of waits due to the inability to obtain locks immediately. Each wait lock value is increased by 1.

MyISAM’s read-write lock schedule is write-first, so the MyISAM engine is not suitable for the main table engine, because after the write lock, other threads can not do anything, a large number of queries can not get the lock, resulting in constant congestion.