锁机制核心功能是用来协调多个会话中多线程并发访问相同资源时,资源的占用问题。锁机制是一个非常大的模块,贯彻MySQL的几大核心难点模块:索引,锁机制,事务。这里是基于MySQL5.6演示的几种典型场景,对面MySQL这几块问题时,有分析流程和思路是比较关键的。在MySQL中常见这些锁概念:共享读锁、排它写锁 ; 表锁、行锁、间隙锁。
2、存储引擎和锁MyISAM引擎:基于读写两种模式,支持表级锁 ;
InnoDB引擎:支持行级别读写锁,跨行的间隙锁,InnoDB也支持表锁 ;
3、锁操作APILOCK TABLE name [READ,WRITE] ;加表锁
UNLOCK TABLES ; 释放标所
二、MyISAM锁机制 1、基础描述MySQL的表级锁有两种模式:共享读锁(Read-Lock)和排它写锁(Write-Lock)。针对MyISAM表的读操作,不会阻塞其他线程对同一表的读请求,但阻塞对同一表的写请求;针对MyISAM表的写操作,会阻塞其他线程对同一表的读和写操作;MyISAM引擎读写操作之间,以及写与写操作之间是串行化。当一次会话线程获取表的写锁后,只有当前持有锁的会话线程可以对表进行操作。其它线程的读、写操作都会等待,直到锁被释放为止。
2、验证案例基于上面的表锁机制特点,使用下面两个案例验证。
基础表结构
CREATE TABLE `dc_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `user_name` varchar(20) DEFAULT NULL COMMENT '用户名', `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表'; CREATE TABLE `dc_user_info` ( `user_id` int(11) NOT NULL COMMENT '用户ID', `city` varchar(20) DEFAULT NULL COMMENT '城市', `country` varchar(20) DEFAULT NULL COMMENT '国家', PRIMARY KEY (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';共享读锁
会话窗口一
-- 1、加读锁 LOCK TABLE dc_user READ ; -- 2、当前会话查询,OK SELECT * FROM dc_user ; -- 4、当前会话写入,Error INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); -- 6、查询其他表,Error SELECT * FROM dc_user_info ; -- 7、释放锁 UNLOCK TABLES ;会话窗口二
-- 3、其他会话查询,OK SELECT * FROM dc_user ; -- 5、其他会话写入,Error INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); -- 8、再次执行写入读取,OK INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998'); SELECT * FROM dc_user ;这里验证表锁的共享读机制。
排它写锁
这里验证表锁的排它写机制。
查询锁争用
通过下面语句查看配置,
show status like 'table%';Table_locks_waited的值越大,锁争用情况越严重,效率则越低下。
3、并发写入问题针对排它写锁的测试案例再说明:在一定条件下,MyISAM表也支持查询和插入操作的并发执行。通过配置系统变量concurrent_insert的值[0,1,2],可以实现并发写入。
concurrent_insert=0,禁止并发写入;
concurrent_insert=1,默认配置AUTO,在MyISAM表中没有空洞,即表的中间没有被删除的行,例如[1,2,3],删除2之后[1,,3],则允许在读表的同时,另一个线程从表尾写入记录。
concurrent_insert=2,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
在下面的例子中,session_1获得了一个表的READ LOCAL锁,该线程可以对表进行查询操作,但不能对表进行更新操作;其他的线程(session_2),虽然不能对表进行删除和更新操作,但却可以对该表进行并发插入操作,这里假设该表中间不存在空洞。
MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。但是当一个读操作和写操作同时请求,写数据会优先获得锁,这一机制可以通过配置修改,指定配置参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET
LOW_PRIORITY_UPDATES=1,使该会话的写操作优先级降低。
指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
5、表锁应用数据一致性校验问题,比如销售量+剩余库存=货品总量,在校验时就要在一次会话中同时锁住订单表和库存表,免得在读取订单表的时候,库存表被修改,导致数据误差出现。
三、InnoDB锁机制 1、事务基础概念事务概念
事务是指作为单个逻辑工作单元执行的一系列操作(SQL语句)。这些操作要么全部成功,要么全部不成功。
事务特性ACID
原子性(Atomicity):事务中的多个操作要么都成功要么都失败
一致性(consistency):事务的执行的前后数据的完整性保持一致
隔离性(isolation):事务执行的过程中,不应该受到其他事务的干扰
持久性(durability):事务一旦结束,数据就持久到数据库
事务问题
脏读:一个事务读到另一个事务没有提交的数据
不可重复读:一个事务前后多次读取相同数据,数据内容不一致,update场景问题
虚读(幻读):一个事务前后多次读取,数据总量不一致,insert场景问题
隔离级别
read uncommitted:事务可以读取另一个未提交事务的数据。
read committed:事务要等另一个事务提交后才能读取数据,解决脏读。
repeatable read:在开始读取数据时,事务开启,不再允许修改操作,解决:脏读、不可重复读。
serializable:最高事务隔离级别,事务串行化顺序执行,解决脏读、不可重复读、幻读。但是效率低下,耗数据库性能。
2、锁机制描述