深入学习mysql(二)

mysql锁

---

一、锁的简介

1.锁的概念

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

在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁对数据库而言显得尤其重要，也更加复杂。

二、MyISAM的表锁

mysql的表级锁有两种模式: 表共享读锁 和 表独占写锁

1.理解读锁和写锁

首先我们创建一张存储引擎为myisam的表并往表中插入几条数据

create table testmysam(
	id int PRIMARY key
)ENGINE=myisam

insert into testmysam values (1),(2),(3)

然后给testmysam加读锁

lock table testmysam READ

如果在当前session中继续进行插入操作,则报错

insert into testmysam values(2)

进行更新操作,也报错

update testmysam set id = 6 where id  = 1

结论: 对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会报错

我们不禁会思考,既然同一个会话不行,那么如果在另一个会话中进行新增操作呢

我们发现此时在另一个会话没有报错,而是进入了等待操作
如果在另一个会话是读操作呢

发现也是可以的,当然,在当前session中查询也是没有问题的

总结:对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求

再回过头来看进行锁操作的会话,如果进行对testmysam 所在数据库下的其他表进行新增操作会如何?你觉得能操作成功吗?

结果是报错

那么对其他表新增不行,查询可以吗?

结果还是报错

但如果我们在另一个会话中插入则可以成功

总结:一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；

上面我们对读锁进行了分析,先使用 unlock TABLES释放下锁,然后接下来我们再看看写锁

lock table testmysam WRITE

我们发现在同一个session中进行新增,编辑和删除和查询都是可以的
但是此时入果查询其他表

可以发现报错了

结论:对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错
此时如果在另一个会话中进行查询操作

我们发现进入了等待状态

总结:对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

三.InnoDb行锁

行锁:

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

注意：

1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

首先我们创建一张表,注意c2加了索引,而c1没加

CREATE TABLE testdemo ( 
`id` int(255) NOT NULL ,
`c1` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,   `c2` int(50) NULL DEFAULT NULL ,
 PRIMARY KEY (`id`), 
 INDEX `idx_c2` (`c2`) USING BTREE 
 )
 ENGINE=InnoDB; 

插入数据

insert into testdemo VALUES(1,'1',1),(2,'2',2);

begin开启一个事务,然后查询testdemo表id=1 的数据

BEGIN 
select * from testdemo where id =1 for update

然后在另一个session 中

update testdemo set c1 = '1' where id = 2 成功 
update testdemo set c1 = '1' where id = 1 等待

id=1等待验证了两个事务不能锁同一个索引

然后rollback结束当前事务,接着

BEGIN 
update testdemo set c1 = '1' where id = 1

注意,这里使用update时后面没有加for update
然后在另外一个 session 中

update testdemo set c1 = '1' where id = 1 等待

我们发现即使没有加for update,一样锁住了

结论: insert ，delete ， update在事务中都会自动默认加上排它锁(写锁)。

继续,先放一下当前表中的数据内容

BEGIN 
update testdemo set c1 = '1' where c1 = '1' 
在另外一个 session 中 
update testdemo set c1 = '2' where c1 = '2' 等待

按道理我在操作从=1时只锁住了c1=1的哪一行,为什么c1=2的这一行也被锁住了呢,因为上面创建表时c1并没有加索引

结论: 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

最后小结

注意 : 行锁释放锁时用rollback或commit,表锁使用 UNLOCK TABLES 或者 begin 来解锁