建设一个网站需要哪些费用,网站建好怎么发布,汕头网站建设推广哪家好,基于superslide2的wordpress幻灯片MySQL中的表级锁 文章目录 MySQL中的表级锁1.MySQL中表级锁的概念2.表锁的概念以及基本使用2.1.表锁的分类以及概念2.2.表锁的使用语法2.3.表共享读锁的基本使用2.4.表独占写锁的基本使用 3.元数据锁的概念以及基本使用3.1.元数据锁的概念3.2.常见的SQL操作所对应的元数据锁3.3…MySQL中的表级锁 文章目录 MySQL中的表级锁1.MySQL中表级锁的概念2.表锁的概念以及基本使用2.1.表锁的分类以及概念2.2.表锁的使用语法2.3.表共享读锁的基本使用2.4.表独占写锁的基本使用 3.元数据锁的概念以及基本使用3.1.元数据锁的概念3.2.常见的SQL操作所对应的元数据锁3.3.元数据锁演示 4.意向锁的概念以及基本使用4.1.意向锁的概念4.2.意向锁的种类4.3.意向共享锁与表读锁兼容的演示4.3.意向排他锁与表锁不兼容的演示 1.MySQL中表级锁的概念
表级锁显而易见就是对于数据库表的一种锁通过表锁可以锁定表避免并发事务下导致表中数据不一致性。
表级锁的力度大针对表上锁后表中的数据可能都无法操作只能读影响的范围大并且发送锁的冲突概率是最高的因为后面还会有行级锁如果通过事务对某一条数据进行了行级锁那么再创建一个表级锁就会导致事务无法提交的情况。
对于MySQL中的表级锁分为以下三种类型
表锁 表锁是针对某张表设置一个lock锁。 元数据锁 元数据锁就是针对表结构的一种锁。 意向锁 对表设置锁时会检查表中每一条数据是否有行级锁可以通过意向锁避免设置表锁时去检查每一条数据是否有行级锁。
2.表锁的概念以及基本使用
2.1.表锁的分类以及概念
表级锁中的表锁又分为两类
表共享读锁read lock表独占写锁write lock
什么是表共享读锁
如下图所示在多个并发客户端的情况下第一个客户端开启了表共享读锁那么DQL查询操作在多个客户端上都可以查询数据不受影响但是当有DDL/DML类的修改表数据的操作时第一个客户端直接会提示表中存在锁第二个客户端的写操作会处于阻塞状态当第一个客户端释放锁之后第二个客户端的写操作才能正常执行。 共享读锁不会影响其他客户端的读但是会阻塞其他客户端的写。
什么是表独占写锁
如下图所示在多个并发客户端的情况下第一个客户端开启了表独占写锁此时第一个客户端上执行DQL查询或者DML修改数据都是不受任何影响但是当第二个客户端要执行查询或者修改数据的操作时就会一直处于阻塞状态只有当第一个客户端释放锁之后第二个客户端才能操作。 独占写锁会阻塞其他客户端的读以及写。
2.2.表锁的使用语法
1创建锁
lock tables 表名 read/write2解锁
unlock tables
或者断开客户端链接2.3.表共享读锁的基本使用 准备两个客户端第一个客户端设置表共享读锁然后分别在所有的客户端上进行读操作观察是否有影响然后分别再所有客户端上执行写操作观察是否有影响。 1.客户端1创建一个读锁
mysql lock tables xscjb read;2.客户端1查询数据
mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 45 | 75 | 93 |
------------------------------3.客户端1写入数据异常
mysql update xscjb set ywcj 99 where xh 1;
ERROR 1100 (HY000): Table xscjb_logs was not locked with LOCK TABLES4.客户端2查询数据
mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 45 | 75 | 93 |
------------------------------5.客户端2写入数据
mysql update xscjb set ywcj 99 where xh 1;
一直处于阻塞中6.客户端1解锁
mysql unlock tables;7.客户端2写入数据成功2.4.表独占写锁的基本使用 准备两个客户端第一个客户端设置表独占写锁然后分别在所有的客户端上进行读操作观察是否有影响然后分别再所有客户端上执行写操作观察是否有影响。 1.客户端1创建一个读锁
mysql lock tables xscjb read;2.客户端1查询数据
mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 45 | 75 | 93 |
------------------------------3.客户端1写入数据正常
mysql update xscjb set ywcj 99 where xh 1;
Query OK, 0 rows affected (0.13 sec)
Rows matched: 1 Changed: 0 Warnings: 04.客户端2查询数据
mysql select * from xscjb limit 1;
处于阻塞5.客户端2写入数据
mysql update xscjb set ywcj 99 where xh 1;
处于阻塞中6.客户端1解锁
mysql unlock tables;7.客户端2写入数据成功3.元数据锁的概念以及基本使用
3.1.元数据锁的概念
元数据锁metadata lock是MySQL系统中自动控制的一种锁无需手动创建默认就存在的一种锁在访问一张数据表时都会自动增加一个元数据锁。
元数据锁又称为MDL锁元数据锁的作用是维护表中元数据与实际数据的一致性元数据可以理解为是表结构当表中存在活动的事务时不可以对元数据进行写入操作。为了避免表中还存在活动事务时元数据被修改的情况从而出现了元数据锁。
通俗一点来说元数据锁就是为了避免DML数据库操作语音与DDL数据库定义语音语句有冲突从而保证数据读写的正确性。 我们可以想一想正在操作表中的数据呢突然表中的元数据发生了改变此时拿到的数据可能就会有异常。 当一张表涉及到未提交事务时这张表的表结构不允许修改。 在MySQL5.5版本中引入了MDL元数据锁的概念当对一张表进行增删改查操作时会默认加一个MDL元数据共享锁当对表结构进行变更操作时会添加一个MDL排他锁。
3.2.常见的SQL操作所对应的元数据锁
SQL锁类型说明lock tables xxx read/writeSHARED_READ_ONLY、SHARED_NO_READ_WRITEselect、select … lock in share modeSHARED_READ与SHARED_READ和SHARED_WRITE类型的锁兼容与EXCLUSIVE类型的锁互斥insert、update、deleet、select … fro updateSHARED_WRITE与SHARED_READ和SHARED_WRITE类型的锁兼容与EXCLUSIVE类型的锁互斥alter table …EXCLUSIVE与任何类型的锁都互斥
当有互斥的操作就说明不兼容此时就会处于阻塞状态。
在元数据锁中对DQL和DML语句又进行了细分DQL类型的语句都是SHARED_READ类型的元数据锁DML类型的语句都是SHARED_WRITE类型的锁这两种类型的锁互相兼容。
3.3.元数据锁演示 当多个事务执行增删改查语句时添加的都是元数据共享锁SHARED_READ/SHARED_WRITE他们之间是相互兼容互不影响的。 #事务一
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 99 | 75 | 93 |
------------------------------
1 row in set (0.00 sec)mysql update xscjb set ywcj 99 where xh 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0mysql commit;
Query OK, 0 rows affected (0.03 sec)#事务二
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 99 | 75 | 93 |
------------------------------
1 row in set (0.00 sec)mysql update xscjb set ywcj 99 where xh 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0mysql commit;
Query OK, 0 rows affected (0.03 sec)只要两个事务修改的不是同一条数据都是互不影响的。 当时当有未提交的事务此时我们去修改表结构修改表结构会产生排他锁排他锁与共享锁不兼容互相排斥此时修改表结构的语句就会一直处于阻塞状态只有当事务全部提交完成后修改表结构的语法才能正常执行。 1.开启一个事务查询xscjb的数据不提交事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from xscjb limit 1;
------------------------------
| xh | xm | ywcj | sxcj | yycj |
------------------------------
| 1 | 小明 | 99 | 75 | 93 |
------------------------------2.此时去修改xscjb表的表结构
mysql alter table xscjb add column pjcj int;
会一直处于阻塞状态修改元数据会产生排它锁排它锁与共享锁互斥此时会处于阻塞状态3.只有当事务全部提交后修改元数据的语句才能成功执行我们还可以通过以下SQL语句看到当前数据库实例中的元数据锁如上图所示select产生的是共享读锁alert产生了排它锁。
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;4.意向锁的概念以及基本使用
4.1.意向锁的概念
意向锁是为了避免DML数据库操作语句在执行过程中出现行锁与表锁的冲突通过意向锁可以在创建表锁时不需要去检查每行数据是否有行锁从而减少表锁检查所带来的性能消耗。
没有使用意向锁之前客户端一给表中加了行锁客户端二要给表加表锁过程如下 首先客户端一开启一个事务在事务中对表中的数据进行增删改查在执行增删改时会对表中的这些数据加一个行锁这时客户端二想在表中添加一个表锁客户端二在添加表锁时就会去检查当前表中每一行数据是否有行锁如果没有则会去添加表锁。 检查每一行数据是否有行锁当表数据量很大时效率很低。 当有了意向锁之后客户端一给表中加了行锁客户端二要给表加表锁过程如下 首先客户端一开启一个事务在事务中对表的数据进行增删改查对数据添加行锁的同时又会给表添加一个意向锁这时客户端二想要在表中添加一个表锁客户端二就会根据客户端一加的意向锁来判断是否可以成功添加表锁并且不会再逐行检查数据是否有行锁效率得到大幅度提升。 简单来说意向锁相当于一个声明根据这个声明添加表锁时就有了参考依据因此不需要逐行判断是否可以添加表锁从而避免了行锁与表锁产生的冲突。
4.2.意向锁的种类
根据意向锁的不同种类可以判断出是否允许对表添加表锁从而避免行锁与表锁的冲突如果不去管这个冲突那么一条数据正在修改突然对表上了锁那么就会产生数据异常的现象。
意向锁的分类
意向共享锁IS 平常的select语句不会自定添加意向共享锁需要在select后面加上lock in share mode参数才能为表建立意向共享锁。 意向排它锁IX insert、update、delete、select…for update等语句都会字段添加一个意向排它锁。
意向共享锁可以与表锁中表共享读锁read兼容但是与表锁中的表独占写锁write互斥当表存意向共享锁时我们可以为表设置一个读锁但是不能设置为写锁一旦表中有意向共享锁时当为表设置写锁时就会处于阻塞状态只有当事务提交后写锁才能正常执行。
意向排它锁既与表共享读锁排斥又与表独占写锁排斥当表中有意向排它锁时都不能为表设置表锁否则就会处于阻塞状态。
当事务提交后意向共享锁、意向排它锁都会自定是否被阻塞的操作此时才能被执行。 表共享读锁可以称为表共享锁表独占写锁也可以称之为表排它锁 4.3.意向共享锁与表读锁兼容的演示
意向共享锁与表锁中的读锁兼容但是与写锁互斥。
首先开启一个事务然后执行select语句产生意向共享锁然后再另一个会话中为表设置表锁观察效果。
1.开启一个事务并执行select产生意向共享锁
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from xscjb where xh 1 lock in share mode;
------------------------------------
| xh | xm | ywcj | sxcj | yycj | pjcj |
------------------------------------
| 1 | 小明 | 99 | 75 | 93 | NULL |
------------------------------------
1 row in set (0.00 sec)2.查询产生的意向共享锁lock_mode为IS就表示为意向共享锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| db_1 | xscjb | NULL | TABLE | IS | NULL |
| db_1 | xscjb | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
-----------------------------------------------------------------------------3.为表设置读锁没有任何问题
mysql lock tables xscjb read;
mysql unlock tables;4.由于意向共享锁与写锁互斥因此写锁一直处于阻塞中只有当事务提交后释放意向锁写锁才能正常执行
mysql lock tables xscjb write;4.3.意向排他锁与表锁不兼容的演示
意向排它锁与表读锁和写锁都互斥都会处于阻塞中。
1.开启一个事务并执行update产生意向排它锁
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql update yexxb set ye 10000 where id 1;2.查询产生的意向共享锁lock_mode为IX就表示为意向排它锁
mysql select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| db_1 | yexxb | NULL | TABLE | IX | NULL |
| db_1 | yexxb | PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
-----------------------------------------------------------------------------4.由于意向排它锁与读锁、写锁互斥因此都会一直处于阻塞中只有当事务提交后释放意向锁读锁、写锁才能正常执行
mysql lock tables yexxb read;
mysql lock tables yexxb write;
