mysql innodb 锁表_MYSQL INNODB 锁表小研究

表结构如下：

delimiter $$

CREATE TABLE `wrox_shop_order` (

`o_id` int(11) NOT NULL AUTO_INCREMENT,

`order_date` datetime DEFAULT NULL,

`order_status` varchar(45) DEFAULT 'CREATED',

`customer_id` int(11) DEFAULT NULL,

PRIMARY KEY (`o_id`),

KEY `orderDate` (`order_date`) //这里最开始不加，后面添上的

) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8$$

这样一个数据表：

开启两个mysql客户端：

因为工作需要 我在mysql workbench里面用了一个 存储过程，相对简单的一个：

delimiter $$/××

××param1 表示输入的值，param2,param3 是输出变量：后面使用

××select @m1as a,@m2 as b; 可以得到输出的值

××/

CREATE PROCEDURE last_delete_info (IN param1 datetime,OUT param2 INT,OUT param3 varchar(45))

BEGINselect o_id,order_status into param2,param3 from wrox_shop_order where order_date=param1 limit 1 forupdate;

#delete from wrox_shop_order where o_id=param2; //这一行后面用@DELETE来表示

END$$

delimiter ;

首先在workBench里执行：

操作一：

set autocommit=0;

call last_delete_info('2013-04-01 19:49:58',@m1,@m2);

select @m1as a,@m2 as b;

结果：

此时 o_id 为3的行是否被锁住了呢？打开mysql 命令行到相应数据库中执行：

操作二：

delete from wrox_shop_order where o_id=3;

结果删除不了数据，说明该行数据被加了锁，那么删除其他数据呢？

delete from wrox_shop_order where o_id=4 or o_id=5;

结果还是删除不了数据，

从这里我们验证了很多人都验证的问题，如果innodb 没有在加索引的行上加锁，那么会使用表锁

接下来如果有注意最开始的创建语句有这么一个：KEY `orderDate` (`order_date`) //这里最开始不加，后面添上的

那么现在去个order_date加个索引：

一个小提示：现在autocomint=0；直接改的话是改不了的。需要commint 后才可以加索引；然后执行：

ALTER TABLE `test`.`wrox_shop_order`

ADD INDEX `order_date` (`order_date` ASC) ;

然后重新执行上面两个mysql客户端工具的操作，执行到第二步的时候命令行还是无法删除，但是如果删除其他数据却是可以的

说明INNODB在加了索引的的行上加锁是加的行级锁，而且是对索引加的锁

今天又做了一些测试，再次总结一下：

比如：

delimiter $$

CREATE TABLE `akulubala` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`artist` varchar(100) NOT NULL,

`title` varchar(100) NOT NULL,

`index_column` tinyint,

PRIMARY KEY (`id`),

KEY `index_c` (`index_column`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8$$

执行：

set autocommit=0;

SELECT * FROM akulubala.akulubala where index_column=9 for update;

另外一个进程执行：

SELECT * FROM akulubala.akulubala where index_column=9 for update; 这里会出现等待。需等待第一个进程commit；

update akulubala set title='s' where index_column=?; 这里不管ID !=9 就可以执行  (说明索引被加了锁)

update akulubala set artist = 'aaaaa' where id = 4;这里 只要ID 行没有在前一个进程搜索的结果中 就不会等待

由以上三点可以得出锁是对索引的锁，即对索引加锁，使用被加锁的索引时都会等待，并且对由索引查出的行加锁,如果更新数据没有用到任何索引，就会全表加锁..

update akulubala set artist='ttt' where id=3;如果id=3不在前一个进程的查询结果中就 可以执行；

单纯的查询如select * from akulubala 是查出所有数据

另外：事务和锁是两回事，事务有四个隔离级别，不同的隔离级别有不同的锁定机制：INNODB 默认是可重复读

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞