Mysql(15)

最新推荐文章于 2024-02-23 11:35:34 发布
人总是要有梦想的QAQ
最新推荐文章于 2024-02-23 11:35:34 发布
阅读量281
点赞数
分类专栏： sql
本文链接：https://blog.csdn.net/qq_43410618/article/details/104309734
版权
sql 专栏收录该内容
51 篇文章 2 订阅
订阅专栏
锁机制


锁机制视为了解决多用户并发访问引起的数据不一致的问题

锁的粒度
锁分为服务器级锁，表级锁，行级锁，
也可以分为隐式锁和显式锁，数据库管理系统会自动未数据加锁，但是有时无法保证数据的一致性，需要手动加锁
锁分为 read lock && write lock,
read lock: A 加上 read lock之后，允许B进行读操作和加read lock，但写操作将会被阻塞
write lock: 会阻塞其他客户机增加读锁和写锁

锁的生命周期：
锁只存在于一个会话内

MYISAM 表级锁
语法：
LOCK TABLES 表1 [AS 别名] READ [LOCAL], 表2 [AS 别名] [LOW_PRIORITY] WRITE,...	
A读锁 A可以读，不可以写，B可以读，可以加读锁，写被阻塞
A写锁 A可以读，可以写，B读写都被阻塞
！！！在加锁期间，只可以对锁定的表进行读写操作
e.g.
ALTER TABLE account ENGINE = MYISAM;
ALTER TABLE classes ENGINE = MYISAM;
LOCK TABLES account AS a READ;
SELECT * FROM account;
SELECT * FROM classes;
UNLOCK TABLES;

如果为同一个表同时加读锁和写锁，需要两个别名
e.g.
LOCK TABLES account AS a READ, account AS b WRITE;
UNLOCK TABLES;

READ LOCAL && READ 区别
READ LOCAL 允许客户机B插入到表的末尾

更新操作比查询操作更重要，如果写锁和读锁同时添加，写锁比读锁优先级高
LOW_PRIORITY 可以降低优先级

LOCK TABLES UNLOCK TABLES 引起事务自动提交

UNLOCK TABLES 所有表都被unlock

客户机关闭， UNLOCK TABLES 将会自动被执行 

INNODB 行级锁
分为共享锁（读锁）、排他锁（写锁）
1.select语句共享锁： SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
2.select语句排他锁： SELECT * FROM table_name WHERE ... FOR UPDATE
3.其他语句自动增加隐式排他锁
e.g.
在A上
START TRANSACTION;
SELECT * FROM student FOR UPDATE;

在B上
START TRANSACTION;
SELECT * FROM student FOR UPDATE;
会被阻塞

直到A进行
COMMIT;
LOCK IN SHARE MODE: A 可以继续进行所有操作，B查询操作可以，其他被阻断
FOR UPDATE:B任何操作都被阻断

选课系统中的行级锁
DESC choose;

DROP PROCEDURE choose_proc;

DELIMITER $$
CREATE PROCEDURE choose_proc(IN s_no CHAR(11), IN c_no INT, OUT state INT)
MODIFIES SQL DATA
BEGIN
	DECLARE avai INT;
	DECLARE have_choose INT;
	DECLARE have_course INT;
	SELECT COUNT(*) INTO have_choose FROM choose WHERE choose.`course_no` = c_no AND choose.`student_no` = s_no;
	IF (have_choose >= 1) THEN
		SET state = -1;
	ELSE 
		SELECT course.`avaible` INTO avai FROM course WHERE course.`course_no` = c_no FOR UPDATE;
		IF (avai <= 0) THEN
			SET state = -2;
		ELSE
			SELECT COUNT(*) INTO have_course FROM choose WHERE choose.`student_no` = s_no;
			IF (have_course >= 2) THEN 
				SET state = -3;
			ELSE
				START TRANSACTION;
				INSERT INTO choose VALUES(NULL, s_no, c_no, NULL, NOW());
			END IF;
			COMMIT;
		END IF;
	END IF;

END;
$$
DELIMITER ;


 意向锁
 意向锁产生的原因是因为：
 A添加了行级锁，B向该表添加表级锁的时候，需要花费大量的资源判端是否存在行级锁，所以A在添加行级锁的时候，会自动的向该表添加表级隐式锁，
 但是意向锁并不能将这个表锁住，因为他是考索引添加行级锁的
 
行级锁与索引之间的关系
A：
USE choose;
ALTER TABLE account SET ENGINE = INNODB;
SET autocommit = 0;
SELECT * FROM account WHERE account_name = '甲' FOR UPDATE;
B：
USE choose;
SET autocommit = 0;
SELECT * FROM account WHERE account_name = '乙' FOR UPDATE;

会发现虽然不同行，但是B会锁等待，原因就是是依靠索引添加的，添加索引：
ALTER TABLE account ADD INDEX account_name_index(account_name);
间隙锁：
当检索条件为某个区间比如account_no = 1 - 100时，会发现除了已有的1 和 2之外，表中不存在的3 4 5 ... 100也被加锁

当事务隔离级别为 REPEATABLE READ 时，添加行级锁默认使用间隙锁，
当隔离级别为 READ UNCOMMITTED 或 READ COMMITTED 默认使用记录锁，只对满足该查询范围的记录添加锁

死锁
A
START TRANSACTION;
SELECT * FROM account WHERE account_no = 20 LOCK IN SHARE MODE;

结果集：
empty SET;

B 
START TRANSACTION;
SELECT * FROM account WHERE account_no = 20 LOCK IN SHARE MODE;

结果集：
empty SET;

A检测到没有记录，向数据库中insert
INSERT INTO account VALUES(20, '丙', 1000);


B检测到没有记录，也向数据库中insert
INSERT INTO account VALUES(20, '丙', 1000);

由于间隙锁的存在，此时便会有死锁现象产生，即两个锁互相牵制，但MySQL会自动检测死锁，通过比较事务权重，对权重值小的进行事务回滚，并释放锁

死锁处理间隙锁之外，加锁时间不对也会导致死锁
e.g.A给B加锁，B给A加锁，就形成了环路死锁
有时候并不能自动检测到死锁，可以通过设置 INNODB 锁等待超时参数 innodb_lock_timeout 的值，设置等待超时阈值

事务的隔离级别
READ UNCOMMITTED 所有事务都可以看到其他未执行事务的执行结果
READ COMMITTED 只可以看到已提交的事务
REPEATABLE READ MYSql默认，确保在同一条事务内相同的查询结果相同
SERIALIZABLE 通过强制事务排序，不可能冲突，即为每一个查询语句添加共享锁，但会导致大量的锁等待现象

脏读，一个事务可以读到另一个事务未提交的结果
不可重复读 在同一条事务内相同的查询结果不相同
幻读 同一个事务中，两条查询语句结果不同，e.g.另一个事务提交后，本事务更新时发现这些数据不同

幻读与不可重复读的区别是
幻读读不到已经提交的数据，不可重复读可以读到其他事务已经提交的数据

设置事务隔离级别

SET GLOBAL | SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE 

脏读
A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@tx_isolation; # 查看隔离级别
START TRANSACTION;
SELECT * FROM account;

B 还未提交的结果A可以检测到
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE account_no = 1;

避免幻读现象的方法：
１.　设置隔离级别为 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
但这种方法会导致大量的锁等待，性能降低
2. 使用间隙锁
SELECT * FROM account WHERE account_no = 200 LOCK IN SHARE MODE;