mysql锁历史记录_史上最全MySQL锁机制

本文主要记录学习MyISAM 和 InnoDB 这两个存储引擎。

为什么要学习锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

因为数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，所以进一步学习MySQL，就需要去了解它的锁机制。

MySQL锁概述：

相对其他数据库而言，MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)；BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。 MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

①：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

②：行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

③：页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。由于BDB已经被InnoDB取代，即将成为历史(所以现在基本都在使用InnoDB存储引擎)。

MyISAN存储引擎

MyISAM 存储引擎只支持表锁，这也是 MySQL 开始几个版本中唯一支持的锁类型。

MySQL表级锁

查询表锁争用情况

mysql> show status like 'table%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Table_locks_immediate | 4 || Table_locks_waited | 0 || Table_open_cache_hits | 4 || Table_open_cache_misses | 8 || Table_open_cache_overflows | 0 |+----------------------------+-------+5 rows in set (0.00 sec)

如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况

也就是说，在MyISAM读模式下，不会阻塞其它用户的同一表读操作，但是会阻塞写操作；而在写模式下，会同时阻塞其它用户同一表的读写操作。

测试MyISAM的写锁

新建一个user表，引擎是MyISAM：

·

mysql> desc user;+---------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | || age | int(3) | YES | | NULL | || address | varchar(60) | YES | | NULL | |+---------+-------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

session A

session B

获得user表的锁锁定

mysql> lock table user write;

Query OK, 0 rows affected (0.00 sec)

mysql>select * from user;

Empty set (0.00 sec)

mysql> insert into user(id, name, age, address) values(1, 'test', 18, 'test address');