Lock. Md

This article is originally published on GitHub open source project: The Path to Java growth welcome everyone star!

The definition of the lock

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads. In a database, in addition to the contention for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and validity of concurrent data access is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of concurrent data access. From this perspective, locks are especially important and complex for databases.

MySQL lock classification

• From the type of operation on data (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 is complete.

• From the granularity of operations on data

• Table locks

• Row locks

Table locks (read first)

The characteristics of

Biased to MyISAM storage engine, low overhead, fast lock; Without a deadlock; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.

When an operation is performed on the MyISAM table, reading the table blocks the write operations of other sessions, and writing the table blocks the read and write operations of other sessions.

grammar

# locked
lock TABLESThe name of the tableread/write;
# check the lock on the table
show open tables;
# release table lock
unlock tables;
Copy the code

Case presentation

• Build table SQL

  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

• Session1 add read lock to mylock table

  LOCK table mylock read;
  Copy the code

  Session1 writes to mylock

  

  Session1 reads and writes to other tables

  

  Session2 read mylock table, no

  Session2 write mylock will block and wait until session1 releases the read lock.

• Session1 add write lock to myLock table

  LOCK table mylock write;
  Copy the code

  Session1 read mylock ok

  

  Session1 write mylock ok

  

  Session1 reads and writes to other tables

  Session2 write myLock blocks and waits until session1 releases the write lock

Row locking (write first)

The characteristics of

Prefer InnoDB storage engine, high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. There are two major differences between InnoDB and MyISAM: One is TRANSACTION support; The second is the use of row-level locking

Case analysis

Build table SQL

/** table test_innodb_lock by shaoxiongdu 2021/10/04 */
create table test_innodb_lock
(
    a int(11),
    b varchar(16))engine = innodb;
insert into test_innodb_lock
values (1.'b2');
insert into test_innodb_lock
values (3.'3');
insert into test_innodb_lock
values (4.'4000');
insert into test_innodb_lock
values (5.'5000');
insert into test_innodb_lock
values (6.'6000');
insert into test_innodb_lock
values (7.'7000');
insert into test_innodb_lock
values (8.'8000');
insert into test_innodb_lock
values (9.'9000');
insert into test_innodb_lock
values (1.'b1');
Copy the code

Start by opening both sessions and turning off their autocommit.

set autocommit=0;
Copy the code

Session1 writes to a row in the table and locks the row automatically

update mylock set b = '4001' where a = 4;
Copy the code

Session2 writes to the row and blocks until session1 commits the transaction.

conclusion

Innodb storage engine implements row-level locking. Although the performance cost of Innodb storage engine may be higher than table level locking, it is far superior to MyISAM table level locking in terms of overall concurrency. When system concurrency is high, Innodb’s overall performance is significantly better than MyISAM’s. However, Innodb’s row-level locking also has its vulnerable side. When used incorrectly, Innodb’s overall performance can not only be as good as MyISAM’s, but can even be worse.

Optimization Suggestions

• As much as possible, make all data retrievals through indexes to avoid non-indexed row locks upgrading to table locks.
• As few retrieval conditions as possible to avoid gap locking
• Try to control the transaction size to reduce the amount of resources locked and the length of time
• Once a row is locked, try not to call another row or table. Immediately dispose of the locked row and release the lock.
• For transactions involving the same table, try to be consistent about the order in which the table is called.
• Transaction isolation is as low as the business environment allows

Page locks

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

This article is originally published on GitHub open source project: The Path to Java growth welcome everyone star!