Mysql中的锁

一、锁的实现

主要介绍 Myisam 和 Innodb这两种存储引擎下的锁，并介绍相关命令；
准备：
创建两个表，分别是 book 表和 student 表，

-- book 表，MyISAM  引擎
CREATE TABLE `book` (	
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bookname` (`book_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- student 表 Innodb 引擎
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

1. Myisam 下的锁（表锁）
Myisam 在执行查询语句（select）前，会自动给涉及的所有表加读锁，其他连接只能对这些表进行读取，如果对这个表进行修改，则会陷入阻塞状态；如图：

左边会话窗口执行 select sleep(20), book_name from book where id=2;是一个耗时查询，在查询期间，右边窗口对id=1的数据进行了修改，可以看到，右边的更新陷入了阻塞状态，左右窗口并没有对同一个数据进行修该，但是出现了这种情况，说明这个查询将整个表锁住了。但是右边是可以查询的，如图：

也可以通过下面的命令进行验证：

lock table 表名称 read/write;	-- 给某个表加锁
unlock tables;		-- 解锁所有表
show open tables; -- 显示所有表的锁状态，是否处于加锁或者未加锁状态，0:未加锁，1：加锁

我们在此通过上面的命令验证加写锁的情况：
执行命令，lock table book write; 对 book 表加写锁，发现右边窗口无法进行查询和修改，都处于阻塞状态；效果如图：


2. Innodb 下的锁（行锁）
使用命令 set autocommit=0;开启手动提交事务。执行第3条和第4条语句，发现第4条处于阻塞状态，这两条语句都是修改id=4的记录。

当第二窗口修改id=5的记录时，可以正常修改，并没有处于阻塞状态。

从上面的测试来看，Innodb下的锁使用的是行锁，并不是表锁。

二、总结

MyISAM的锁机制：分为共享读锁和独占写锁。

• 读锁是：当某一进程对某张表进行读操作时（select），其他线程也可以读，但是不能写。简单的理解就是，我读的时候你不能写。
• 写锁是：当某一进程对某种表某张表的写时（insert，update，，delete），其他线程不能写也不能读。可以理解为，我写的时候，你不能读，也不能写。

因此MyISAM的读操作和写操作，以及写操作之间是串行的！MyISAM在执行读写操作的时候会自动给表加相应的锁（也就是说不用显示的使用lock
table命令），MyISAM总是一次获得SQL语句所需要的全部锁，这也是MyISAM不会出现死锁的原因。

InnoDB实现了两种类型的行锁。

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，但是组织其他事务获得相同数据集的共享锁和排他锁。

可以这么理解：
共享锁就是我读的时候，你可以读，但是不能写。排他锁就是我写的时候，你不能读也不能写。其实就是MyISAM的读锁和写锁，但是针对的对象不同了而已。

表锁和行锁的特点：

• 表锁的特点：开销小，不会产生死锁，发生锁冲突的概率高，并且并发度低。
• 行锁的特点：开销大，会产生死锁，发生锁冲突的概率低，并发度高。