数据库考点_6

MyISAM和InnoDB关于锁方面的区别是什么

MyISAM默认使用的是表级锁,不支持行级锁.

InnoDB默认使用的是行级锁,也支持表级锁.

下面的东西针对于MyISAM引擎:

什么是表级锁

举例:

前提: 我当前有一张表student,使用的是MyISAM引擎.

我们在这里模拟一个并发操作哈:

当我们对表进行查找操作的时候,倘若我们在查找操作还没有结束,还在执行的过程中去执行一个修改操作,我们会发现 只有当我们的查询操作结束后,才会执行我们修改操作的指令.

这是因为我们在执行查询语句的时候,MyISAM自动地给我们的student表上了一个表锁,也就是说,它会锁住这张表,不允许此时其他对表的更新操作(增删改)

其实是这样子的:
当我们对数据库表使用查询语句select的时候,它会为该数据库表加上一个 表级的读锁

当我们对数据库表进行增删改的时候,它会自动为该数据库表加上一个表级的写锁

当我们的查询操作还没有执行结束,即读锁还没有被释放的时候,我们去执行增删改操作,这时MyISAM会给数据库表添加写锁,但是读锁还没有释放,所以添加写锁的操作就会被阻塞,直到所有的读锁都被释放为止.

如何显式地给数据库表添加表级锁

添加读锁: LOCK TABLES 表名 READ;

添加写锁: LOCK TABLES 表名 WRITE;

释放锁: UNLOCK TABLES;

读锁也叫作共享锁: 即我并发的同时执行两个读取操作(执行查询语句会自动添加写锁),两个读操作不会有影响(也就是说支持同时上两个共享锁),不会像更新操作那样要等一个执行结束才执行另一个.

写锁也叫作排它锁: 即我并发的同时执行两个更新操作(更新操作会自动添加写锁),要等先执行的那一个执行结束,释放了写锁后,执行另一条更新语句才会被执行,此时数据库表再次被加上一个写锁.

MyISAM引擎注意事项:

1. 一个数据库表同一时间只能上一个锁.
2. 它只支持表级锁,不支持行级锁,也就是说,它会锁住整张表.
3. 它不支持事务管理,所有对数据库的操作都会直接影响数据库的数据.
4. 支持同时上多个共享锁,即可以多次读取,互相不影响,但是如果先上的是排它锁,那么其他的任何操作都是不允许的,必须等排它锁被释放才可以.
5. 我们也可以为查询语句加上排它锁:

例如:当执行了select * from user where id between 1 and 200000 for update语句后,user表就被添加了排它锁,此时,我们在该查询语句没有结束前再并发地执行另一条查询语句的话,那么另一条查询语句就只能等之前的查询语句执行结束后释放了排它锁之后才能执行.

下面的东西针对于InnoDB引擎:

上面的共享锁和排它锁在InnoDB中也有,效果也一样(比如加了共享锁不能加排它锁啊,可以同时加共享锁啊这些都一样),不过还是有些许差别(比如select语句不自动加共享锁等).

InnoDB支持事务管理,MySQL默认自动提交事务,而且只有当前事务commit了之后,锁才会被释放.

例子:
我们同时对一个表进行两次更新操作,我们会发现,两次更新操作是互不影响的,这如果在MyISAM引擎中是不应该发生的.

这种情况主要是因为,InnoDB使用的是二段锁.

什么是二段锁

二段锁也就是说: 加锁和解锁是分成两个步骤来进行的,即先对同一个事务里的一批操作分别加锁,然后在提交commit的时候,再对事务里加上的锁进行统一的解锁. 由于mysql默认自动提交,所以看起来和MyISAM没啥区别.

几条mysql命令

查看自动提交的状态:
SHOW VARIABLES LIKE ‘autocommit’

关闭自动提交:
SET autocommit = 0;

打开自动提交:
SET autocommit = 1;

手动添加排它锁:
select * from person where id = 3 for update;

手动添加共享锁同时验证行级锁:
select * from person where id = 3 lock in share mode;

执行上面的语句之后,就表示给id=3的那一行添加了共享锁,此时我们对id=3的那一行进行更新操作会被阻塞,只有当我们对当前事务进行了提交(共享锁被释放)之后,更新操作才会进行.但是,如果我们是并发的对id=4(只要不等于3就行)进行增删查改操作的话,丝毫不会受到影响,这就说明InnoDB默认支持行级锁

InnoDB中对select语句进行了特殊处理

例子如下:
我们将数据库设置为不自动提交后

如果我们对数据库中某行进行查询操作,它不会自动添加共享锁
然后我们并发的对该行进行更新操作,我们发现更新操作不受查询操作的影响,amazing!
这就是因为InnoDB中对select语句进行了改进,使得select没有对该行上锁,所以才导致更新是成功的,这个我之后再写

小知识

1. 当用到表级锁的时候,只要操作了表里的数据,表都会被锁住,所以表级锁与索引无关.
2. 当查询语句用到了索引的时候,用的时行级锁,即只对操作的这一行加锁,对其他行的操作不会有影响.
3. 当查询语句不走索引的时候,用的是表级锁.
4. MyISAM使用的是非聚簇索引(索引保存数据文件的指针和数据分开放,主键索引和辅助索引都可以直接拿到数据,在纯检索,即增删改很少的系统中性能比InnoDB要好),InnoDB有且仅有一个聚簇索引(数据文件和索引放在一起的,必须要有主键,通过主键索引效率很高,但是当用到辅助索引的时候需要查两次,)
5. 不论是表级锁还是行级锁,都分为共享锁和排它锁.
6. 共享锁简写是S,排它锁简写是X,这都是针对于行的行级锁.
7. 意向共享锁IS,意向排他锁IX,这是InnoDB针对于表的表级锁.
   

是不是行级锁一定比表级锁要好?

不是的

因为锁的范围越小,代价越高,相比于表级锁在表的头部直接加锁来讲,行级锁还要扫描到某行然后对其上锁,代价比表级锁大多了

同时InnoDB支持事务,相比于MyISAM也带来了更大的开销.