Mysql Lock Summary

Lock

MyISAM only supports table locks

createtable crm_member; ##查看引擎altertable crm_member engine = MyISAM; ##更改表引擎

Read Lock Demo

Session 1 Read lock Other session can be read and not writable

lock table crm_member read;

Session 1 Lock Wait time setting

select @@global.lock_wait_timeout; ##查看锁等待时间设置set @@global.lock_wait_timeout = 20; ##设置锁等待时间为20秒

Session 2 query data does not affect

MariaDB [memberselectfromwhereid4;+------+| name |+------+222  |+------+1rowinset (0.00 sec)

Session 2 Update data

MariaDB [member]> MariaDB [member]> update crm_member set name = ‘222‘ where id = 4;## 进入锁等待

Session 1 Viewing process

97 | root | localhost           memberQuery   |   120fortablelevellockupdateset‘222‘whereid4 |    0.000 |## 等待一个表级别的锁

Session 1 Release Lock

MariaDB [memberunlocktables;Query0rows affected (0.00 sec)

Session 2 Get lock Update data immediately

MariaDB [member]> update crm_member set name = ‘222‘ where id = 4;Query OK, 0 rows affected (3 min 9.38 sec)Rows matched: 1  Changed: 0  Warnings: 0

Session 2 Lock Wait timeout

MariaDB [memberupdateset‘222‘whereid41205Locktimeouttransaction

Write lock Demo

Session 1 Adding a write lock

MariaDB [memberlocktablewrite;Query0rows affected (0.00 sec)

Session 1 Adding a write lock

MariaDB [memberlocktablewrite;Query0rows affected (0.00 sec)

Session 2 reading data

MariaDB [member]> select name from crm_member where id = 4;

Session 1 Viewing process

MariaDB [member]> show processlist;| 109 | root | localhost           | member | Query   |     2 | Waiting for table metadata lock | select name from crm_member where id = 4 |    0.000 |## 等待一个元数据的锁

Session 2 Read Data lock wait timeout

MariaDB [member]> select name from crm_member where id = 4;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB locks support both table locks (same as MyISAM) and row locks. Row It lock demo

Mode 1 Session 1 direct Update other session can not read non-writable unless snapshot read

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> update crm_member set name = ‘aaaaaa‘ where id = 4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

Mode 2 Session 1 For-update Other session cannot be read not writable unless snapshot read

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> select name from crm_member where id = 4 for update;

Session 2 Mode 1 transaction Mode start update operation same record

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;## 进入锁等待

Session 2 Mode 2 Normal update operation

MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;

Session 1 Viewing process

MariaDB [member]> Show processlist;+-----+------+---------------------+--------+---------+-------+----------+---------------------------------- ---------------+----------+|Id|User| Host | db | Command | Time| State | Info | Progress |+-----+------+---------------------+--------+---------+-------+----------+---------------------------------- ---------------+----------+|109| Root | localhost |member|Query|2| Updating |UpdateCrm_memberSetName =' CCC ' where ID=4|0.000|+-----+------+---------------------+--------+---------+-------+----------+---------------------------------- ---------------+----------+# # There's an update waiting

Session 2 Lock Wait timeout

MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session 1 Viewing data has changed

MariaDB [member]> select name from crm_member where id = 4;+------+| name |+------+| ccc  |+------+

Session 2 read data not changed can only read UNCOMMITTED version of data

MariaDB [member]> select name from crm_member where id = 4;+------+| name |+------+| bbb  |+------+

Mysql Lock Summary