MySQL锁的基本介绍

1.mysql锁的基本介绍:
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点的是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
从上述特点,很难笼统地说哪种锁更好,只能具体应用的特点来说哪种锁更合适:但从锁的角度 来说: 表级锁更适合于以查询为主,只有少量按索引

2.MyISAM表锁
MyISAM的表级锁有两种模式:表共享读锁和表独占写锁
对MySAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对MyISAM表的写请求;则会阻塞其他用户对于同一表的读和写操作:MyISAM表的读操作和写操作之间,以及写操作之间是串行的!

MyISAM写锁阻塞读的案例
首先创建数据库表,存储引擎使用MyISAM

CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO mylock (id, NAME) VALUES (‘1’, ‘a’);
INSERT INTO mylock (id, NAME) VALUES (‘2’, ‘b’);
INSERT INTO mylock (id, NAME) VALUES (‘3’, ‘c’);
INSERT INTO mylock (id, NAME) VALUES (‘4’, ‘d’);

首先我们在会话一中设置表写锁:lock table mylock write;

我们在会话二中进行查询此表,发现被阻塞了

此时我们释放会话一的表写锁:unlock tables,发现会话二的读操作结果便出来了


同样的操作,我们对会话一进行表写锁,会话二的update操作也被阻塞,直到会话一的锁释放,会话二的update操作才能够继续进行下去。

MyISAM读阻塞写的案例
首先给会话一加上读锁:lock table mylock read

我们发现无论在会话一还是会话二中,对于此表的读操作都没有被阻塞:


此时我们对会话二的写操作会出现阻塞

而我们对会话一的写操作则会直接报错:

错误提示也很明显,告诉已经被读锁占用,无法被修改(写入)
由此我们可见,MyISAM的写锁会阻塞其他会话的读写操作,而不会阻塞当前会话的任何操作,而MyISAM的读锁则会阻塞其他会话的写操作,而不会阻塞所有的读操作。但是我们也发现了一个问题,这种表级别的锁过于暴力,当一个会话上了读锁之后,所有的会话都无法做写操作,这是效率极其低下的。

MySAM在执行查询语句之前,会自动给涉及的所有表加读锁,在执行更新操作前,会自动给涉及的所有表加写锁,这个过程并不需要用户干预,因此用户一般不需要命令来显示的加锁

MyISAM表的读和写时串行的,这是就总体而言的,在一定条件下,MyISAM也支持查询和插入操作的并发执行:

首先我们在会话一先加上本地的读锁:lock table mylock read local

当前会话无法对数据进行任何的写操作

而在会话二中我们可以进行写入操作

但是会话一在未释放锁时却无法查询到此条数据

而当会话一释放了读锁之后,就可以查看到这条数据了

InnoDB的行锁模式及加锁方法
共享锁: 又称读锁。允许一个事务去读一行,组织其他事务获得相同数据集的排它锁.若事务T对对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
排他锁: 又称写锁,允许获取其他锁的事务更新数据,阻止其他事务取得相同的数据集共享共享锁和排他写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他排他锁可以做select***for update 语句,加共享锁可以使用select *** lock in share mode语句。所以加排他所的数据行在其他事务中是不能修改数据的,也不能通过for update和lock in share mode 锁的方式查询数据,但可以直接通过select ** from 查询数据,因为普通查询没有任何锁机制

InnoDB行锁的实现方式
InnoDB行锁时通过给索引上的索引项加锁来实现的,这一点MySQL和Oracle不同,后者是通过在数据块中相应数据行加锁来实现的。InnoDB这种行锁特点意味着,只有通过条件索引检索数据,InnoDB才使用行锁,否则,InnoDB将使用表锁

首先我们演示不通过索引条件查询的时候,innodb使用的是表锁而不是行锁:
create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,‘1’),(2,‘2’),(3,‘3’),(4,‘4’);

我们在会话一中id为1使用排他锁:for update

我们在会话二中id为2使用排它锁,然而我们发现,会话二的行锁排他锁被阻塞了

因此我们得出结论,当不存在索引的情况,Innodb是为表加上表锁;

其次我们演示带有索引的行锁排他锁,首先创建表:
create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,‘1’),(2,‘2’),(3,‘3’),(4,‘4’);

我们同样在会话一中对id为1的数据进行行锁排他锁:

其次我们对会话二中对id为2的数据进行行锁排他锁:

此时我们发现,id为2的数据在会话二中正常上锁,此时可以证明当存在索引的时候,Innodb使用的是行级锁.

下面我们证明Innodb的行锁时对索引进行上锁,而不是对数据行进行上锁,我们向数据库插入一条id为1,值为4的数据
insert into tab_with_index values(1,‘4’);
此时我们在会话一对id为1,name为ll的数据进行行锁

其次,我们在会话二对id为1,name为4的数据进行行锁:

此时我们惊奇的发现,对id为1,name为4的数据进行行锁的时候,会话二被阻塞了,这是因为id列是索引列,而Innodb的行锁就是对索引进行上锁,因此会话二会被阻塞.