mysql普通查询排他锁,Mysql锁(表级锁页级锁行级锁)

Mysql锁(表级锁页级锁行级锁)

Mysql锁(表级锁，页级锁，行级锁)

一、表级锁，页级锁，行级锁

1、表级锁定(table-level):引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行

仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。

详情:表级锁是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，所以获取锁和释放锁的速度很快。

优点是表级锁会一次将整个表锁定，所以可以很好的避免死锁的问题。

缺点是锁定颗粒度大是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

表级锁有两种模式 :表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)

(1)表共享读锁:进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作

用法： LOCK TABLE table_name [ AS alias_name ] READ

指定数据表，LOCK类型为READ即可，AS别名是可选参数，如果指定别名，使用时也要指定别名才可

例子:

查询操作失败:

查询操作成功:

不加别名修改失败:

加别名修改失败:

权限:

写操作

读操作

使用读锁线程

否(报错)

能

不使用读锁线程

否(阻塞)

能

前提：当前没有线程对该数据表使用写锁，否则申请会阻塞。

(2)表独占写锁：对MyISAM表的写操作，则会阻塞其他用户对同一表的读写操作。

用法： LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE

别名用法与读锁一样，写锁增加了指定优先级的功能，加入LOW_PRIORITY可以指定写锁为低优先级。

例子:

加别名查询操作成功:

不加别名查询操作失败:

加别名修改成功:

不加别名修改失败:

前提： 当没有线程对该数据表使用写锁与读锁，否则申请回阻塞。

权限:

写操作

读操作

使用写锁线程

能

能

不使用写锁线程

否(阻塞)

能(阻塞)

对于使用写锁的MySQL线程，其可以对锁定表进行读写操作。但是对于其他线程，对指定表读写操作都是非法的，需要等待直到写锁释放。

锁分配的优先级： LOW_PRIORITY WRITE < READ < WRITE

注意:相同优先级的锁申请，分配原则为谁先申请，谁先分配

(3)注意:

申请锁时使用别名，使用锁定表时必须加上别名

原因:

a、 有可能不能操作(查询或更新)没有被锁定的表。

例如当只申请table1的读锁，SQL语句中包含申请的表的话，那么对table2的操作是非法的。例如：

解决这个问题的方法是使用别名，如果多次使用到一个表，需要声明多个别名。

详情:行级锁对于锁定对象的颗粒度来说是很小的。

优点是锁定颗粒度很小，所以发生锁定资源争用的概率也最小，因此在在并发处理能力上面有较大的优势。

缺点是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了，比页级锁和表级锁消耗更多的内存。此外，行级锁定也最容易发生死锁。

行级锁有两种模式 :共享锁  和  排他锁

(1)共享锁又称为读锁，就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

用法:select ... lock in share mode

修改失败:

共享查成功:

用法:mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，如果加排他锁可以使用select ...for update语句

测试排他锁例子:

使用begin开启事务，不关闭事务，因为提交事务或回滚事务就会释放锁。

注:排他锁的启用，除了for update 外还有insert，update， delete

打开另一个查询窗口，对同一数据分别使用排他查和共享锁查询两种方式查询

排他查(失败):

共享查(失败):

普通查询成功:

因为排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁 ，但是普通查询可以，因为普通查询默认不加任何锁。

但查到的数据是修改数据之前的老数据

(3)注意:

a、select语句默认不会加任何锁类型

b、排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

3、页级锁定(page-level):引擎 BDB。

直接锁定整张表，其它进程无法对该表进行写操作。

如果你是写锁，则其它进程则读也不允许。

详情:页级锁是MySQL中比较独特的一种锁定级别，页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

另外，页级锁定和行级锁定一样，会发生死锁。

二、锁定粒度大小比较

表级锁定 > 页级锁定 > 行级锁定

Mysql锁(表级锁页级锁行级锁)相关教程