MySQL账户锁定15分钟

转载

jkfox 2024-09-23 12:51:03

文章标签 MySQL账户锁定15分钟 mysql 数据库锁读锁 文章分类 MySQL 数据库

文章目录

MySQL锁

为什么要锁？
MySQL锁介绍

锁的划分类别
表级锁和行级锁之间的区别

MySQL表级锁介绍

1.MySQL的表级锁有两种:
2.查看表级锁定的争用状态变量
3.表锁介绍
4.实例

4.1创建表并添加数据
4.2表读锁
4.3表写锁
4.4元数据锁

疑问
MySQL行级锁介绍

1.InnoDB行级锁，
2.查看InnoDB所使用的行级锁定争用状态查看:
3.两阶段锁
4.行读锁
5.行读锁升级为表锁
6.行写锁
7.按锁定范围区分

7.1创建表
7.2记录锁(Record Lock)
7.3间隙锁(Gap Lock)
7.4临键锁(Next-Key Locks)

MySQL锁

为什么要锁？

事务并发

幻读：主要是在另一个事务在查询时插入数据
脏读：主要是在另一个事务在查询时更新数据
不可重复读：主要是在另一个事务在查询时更新数据并提交

事务并发的三大问题都是数据库读一致性问题:必须有数据库提供一定的事务隔离机制来解决。

事务隔离

事务隔离级别	脏读	不可重复读	幻读
未提交读(Read Uncommitted)	可能	可能	可能
已提交读(Read Committed)	不可能	可能	可能
可重复读(Repeatable Read)	不可能	不可能	对InnoDB不可能
串行化(Serializable)	不可能	不可能	不可能

事务隔离方案

当前读:在读取数据前，对其加锁，组织其他事务对数据进行修改——Lock Based Concurrency Control(LBCC)
快照读:生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取——Multi Version Concurrency Control(MVCC)

MySQL锁介绍

锁的划分类别

按照锁的粒度来分，有三种级别的锁定机制:

全局锁：锁的是整个database。由MySQL的SQL layer层实现的。
表级锁：锁的是某个table。由MySQL的SQL layer层实现的。
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB

按照锁的功能

共享读锁
排他写锁

按照锁的实现方式

悲观锁
乐观锁

表级锁和行级锁之间的区别

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。

MySQL表级锁介绍

1.MySQL的表级锁有两种:

表锁
元数据锁(meta data lock,MDL)

2.查看表级锁定的争用状态变量

SHOW STATUS LIKE 'table%';

结果集:

Table_locks_immediate:产生表级锁定的次数
Table_locks_waited:出现表级锁定争用而发生等待次数

3.表锁介绍

表锁有两种:

表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)

手动增加表锁

lock table 表名称 read(write),表名称2 reda(write),其他；

查看表锁情况

SHOW OPEN TABLES;

删除表锁

UNLOCK TABLES;

4.实例

4.1创建表并添加数据

-- 创建mylock表
CREATE TABLE mylock(
	id INT(11) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT NULL,
	PRIMARY KEY (id)
);

-- 为mylock表添加数据
INSERT INTO mylock (id ,NAME) VALUES (1 ,'a');
INSERT INTO mylock (id ,NAME) VALUES (2 ,'b');
INSERT INTO mylock (id ,NAME) VALUES (3 ,'c');
INSERT INTO mylock (id ,NAME) VALUES (4 ,'d');

4.2表读锁

-- 会话1：给mylock表加读锁
LOCK TABLE mylock READ;

-- 会话1：可以查询已经加锁的表
SELECT * FROM mylock;

-- 会话1：不能查询非锁定表
SELECT * FROM tdep;

-- 会话2:可以查询，没有锁
SELECT * FROM mylock;

-- 会话2：不能查询非读锁的表
SELECT * FROM tdep;

-- 会话2:修改阻塞，自动加行写锁
UPDATE mylock SET NAME = 'x' WHERE id = 2;

-- 会话1：释放表锁
UNLOCK TABLES;

-- 会话2：修改执行完成
UPDATE mylock SET NAME = 'x' WHERE id = 2;

-- 返回结果
1 queries executed, 1 success, 0 ERRORS, 0 WARNINGS

4.3表写锁

-- 会话1：给mylock表添加写锁
LOCK TABLE mylock WRITE;

-- 会话1：可以查询该表
SELECT * FROM mylock;

-- 会话1：不能访问非锁定表
SELECT * FROM tdep;

-- 会话1：修改语句可以执行
UPDATE mylock SET NAME = 'y' WHERE id = 2;

-- 会话2：不能查询未加锁的表
SELECT * FROM tdep;

-- 会话2：查询阻塞
SELECT * FROM mylock;

-- 会话1：释放表锁
UNLOCK TABLES;

-- 会话2：正常执行查询
SELECT * FROM mylock;

4.4元数据锁

MDL在访问一个表的时候会自动加上。MDL的作用是确保读写的正确性。
读锁之间不互斥，读写锁之间，写锁之间互斥。
MySQL5.5，当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构变更操作时，加MDL写锁。

-- session1：开启事务
BEGIN;

-- session1：加MDL读锁
SELECT * FROM mylock;

-- session2：可以读表
SELECT * FROM mylock;

-- session2：修改阻塞(后面的会话不论查询还是修改都会阻塞)
ALTER TABLE mylock ADD f INT;

-- session1：修改成功后session2也执行完毕
ALTER TABLE mylock DROP c;

-- session1：提交事务或者rollback回滚事务从而释放读锁
ROLLBACK;
COMMIT;

疑问

当session1在执行ALTER TABLE mylock DROP c;语句后session2自动解锁，执行语句ALTER TABLE mylock ADD d INT;修改表。

MySQL行级锁介绍

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

1.InnoDB行级锁，

按照锁定范围来分，有三种:

记录锁（Record Locks）：锁定索引中的一条记录。
间隙锁（Cap Locks）：要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
临键锁（Next-Key Locks）：是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

按照锁定功能来分，有两种:

共享锁（s）Share Locks：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（x）Exclusive Locks：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于update，delete，insert语句，InnoDB会自动动给涉及数据集加排他锁(x)
而对于普通的select语句，InnoDB不会加任何锁

-- 手动添加共享锁
select * from table_name where …… lock in share mode

-- 手动添加排他锁
select * from table_name where …… for update

2.查看InnoDB所使用的行级锁定争用状态查看:

show status like 'innodb_row_lock%';

结果集；

Innodb_row_lock_current_waits:当前正在等待锁定的数量

Innodb_row_lock_time:从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg:每次等待所花平均时间

Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间

Innodb_row_lock_waits:系统启动后到现在总共等待的次数

3.两阶段锁

加锁阶段：只加锁，不放锁
解锁阶段：只放锁，不加锁

4.行读锁

使用索引加行锁，未加锁的行可以访问

-- 查看行锁状态
SHOW STATUS LIKE 'innodb_row_lock%';

-- 会话1：开启事务
BEGIN;

-- 会话1：手动加id=1的行读锁，使用索引
SELECT * FROM mylock WHERE id = 1 LOCK IN SHARE MODE;

-- 会话2：开启事务
BEGIN;

-- 会话2：对未锁定行可以修改
UPDATE mylock SET NAME = 'z' WHERE id = 2;

-- 会话2：对加锁行可以查询
SELECT * FROM mylock WHERE id = 1;

-- 会话2：对锁定行修改阻塞
UPDATE mylock SET NAME = 'y' WHERE id = 1;

-- 会话1：回滚事务或提交事务
ROLLBACK;
COMMIT;

-- 会话2：回滚事务或提交事务
ROLLBACK;
COMMIT;

5.行读锁升级为表锁

未使用索引行锁升级为表锁

-- session1:开启事务
BEGIN;

-- session1:手动加name='c'的行读锁，未使用索引
SELECT * FROM mylock WHERE NAME = 'c' LOCK IN SHARE MODE;

-- session2:开启事务
BEGIN;

-- session2:可以查询
SELECT * FROM mylock WHERE NAME = 'c';

-- session2:修改阻塞，未使用索引行锁升级为表锁
UPDATE mylock SET NAME = 'b' WHERE  id = 2;

-- session1:提交事务或回滚事务
ROLLBACK;
COMMIT;

-- session2:提交事务或回滚事务
ROLLBACK;
COMMIT;

6.行写锁

主键索引产生记录锁

-- session1:开启事务
BEGIN;

-- session1:手动加id=1的行写锁
SELECT * FROM mylock WHERE id = 1 FOR UPDATE;

-- session2：开启事务
BEGIN;

-- session2:可以访问
SELECT * FROM mylock WHERE id = 2;

-- session2:可以读，不加锁
SELECT * FROM mylock WHERE id = 1;

-- session2:可以读，加锁阻塞
SELECT * FROM mylock WHERE id = 1 LOCK IN SHARE MODE;

-- session1:提交或回滚事务
ROLLBACK;
COMMIT;

-- session2:事务提交或回滚
ROLLBACK;
COMMIT;

7.按锁定范围区分

MySQL账户锁定15分钟_mysql

7.1创建表

-- 创建表news
CREATE TABLE news(
	id INT(11) PRIMARY KEY ,
	number INT(11)
);
	
-- 添加数据
INSERT INTO news(id,number) VALUES(1,2);
INSERT INTO news(id,number) VALUES(3,4);
INSERT INTO news(id,number) VALUES(6,5);
INSERT INTO news(id,number) VALUES(8,5);
INSERT INTO news(id,number) VALUES(10,5);
INSERT INTO news(id,number) VALUES(13,11);

-- 添加非唯一索引
ALTER TABLE news ADD INDEX inx_num(number);

7.2记录锁(Record Lock)

-- 记录锁:只对指定索引的行加锁
-- session1:开启
START TRANSACTION;

-- session1:唯一性索引(唯一/主键)等值查询，精准匹配
SELECT * FROM news WHERE id = 3 FOR UPDATE;

-- session2:非锁定行可以修改
UPDATE news SET number = 8 WHERE id = 8;

-- session2:修改id阻塞
UPDATE news SET number = 5 WHERE id = 3;

-- session1:提交事务或回滚事务
ROLLBACK;
COMMIT;

7.3间隙锁(Gap Lock)

间隙表id和number都在间隙内则阻塞

-- session1:
START TRANSACTION;

-- session1:手动添加行写锁
SELECT * FROM news WHERE number = 4 FOR UPDATE;

-- session2：
START TRANSACTION;
-- session2:执行阻塞
INSERT INTO news VALUE(2,4);
INSERT INTO news VALUE(2,2);
INSERT INTO news VALUE(4,4);
INSERT INTO news VALUE(4,5);

-- session2:执行成功
INSERT INTO news VALUE(7,5);
INSERT INTO news VALUE(9,5);
INSERT INTO news VALUE(11,5);

-- session1:提交事务或回滚事务
ROLLBACK;
COMMIT;

-- session2:提交事务或回滚事务
ROLLBACK;
COMMIT;

主键索引产生间隙锁

-- session1:开启事务
START TRANSACTION;

-- session1:手动添加主键索引间隙
SELECT * FROM news WHERE id > 2 AND id < 8 FOR UPDATE;

-- session2:
START TRANSACTION;

-- session2:执行成功
INSERT INTO news VALUE(9,8);
UPDATE news SET number = 10 WHERE id = 10;

-- session2:执行阻塞
UPDATE news SET number = 8 WHERE id = 8;
INSERT INTO news VALUE(2,1);
INSERT INTO news VALUE(7,3);

-- session1:提交事务或回滚事务
ROLLBACK;
COMMIT;

-- session2:提交事务或回滚事务
ROLLBACK;
COMMIT;

非主键索引产生间隙锁，影响范围

-- session1:开启事务
START TRANSACTION;

-- session1:由于number最大为11，所以锁住了(11,+oo)
SELECT * FROM news WHERE number = 13 FOR UPDATE;

-- session2:
START TRANSACTION;

-- session2:执行成功:number不在(11,+oo)内
INSERT INTO news VALUE(11,11);
INSERT INTO news VALUE(14,3);
INSERT INTO news VALUE(15,10);
UPDATE news SET number = 10 WHERE id = 10;
UPDATE news SET number = 1 WHERE id = 13;

-- session2:执行阻塞
UPDATE news SET number = 12 WHERE id = 13;-- id为13是number11的范围不允许执行
INSERT INTO news VALUE(12,12);-- number12大于11了，不允许执行

-- session1:事务提交或回滚
ROLLBACK;
COMMIT;

-- session2:事务提交或回滚
ROLLBACK;
COMMIT;

7.4临键锁(Next-Key Locks)

-- session1:开启事务
START TRANSACTION;

-- session1:锁住（3,6]
SELECT * FROM news WHERE id > 3 AND id < 6 FOR UPDATE;

-- session1:（3,6]添加
INSERT INTO news(id,number) VALUES(4,2);

-- session1:(3,6]以外成功修改
UPDATE news SET number = 8 WHERE id = 8;

-- session1:查询已经更改
SELECT * FROM news;

-- session2：开启事务
BEGIN;

-- session2:（3,6]以外修改成功
UPDATE news SET number = 5 WHERE  id = 3;

-- session2:（3,6]以外添加成功
INSERT INTO news(id,number) VALUES(7,2);

-- session1:查询已经更改
SELECT * FROM news;

-- session2:（3,6]以外修改成功
UPDATE news SET number = 9 WHERE  id = 8;

-- session2:（3,6]修改阻塞
UPDATE news SET number = 7 WHERE  id = 6;

-- session2:（3,6]添加阻塞
INSERT INTO news(id,number) VALUES(4,2);

-- session1:提交事务或回滚事务
ROLLBACK;
COMMIT;

-- session2:事务提交或回滚
ROLLBACK;
COMMIT;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：服务器装redis什么版本比较好

下一篇：浏览器写入session信息

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

MySQL账户锁定15分钟

MySQL账户锁定15分钟

文章目录

MySQL锁

为什么要锁？

MySQL锁介绍

锁的划分类别

表级锁和行级锁之间的区别

MySQL表级锁介绍

1.MySQL的表级锁有两种:

2.查看表级锁定的争用状态变量

3.表锁介绍

4.实例

4.1创建表并添加数据

4.2表读锁

4.3表写锁

4.4元数据锁

疑问

MySQL行级锁介绍

1.InnoDB行级锁，

2.查看InnoDB所使用的行级锁定争用状态查看:

3.两阶段锁

4.行读锁

5.行读锁升级为表锁

6.行写锁

7.按锁定范围区分

7.1创建表

7.2记录锁(Record Lock)

7.3间隙锁(Gap Lock)

7.4临键锁(Next-Key Locks)

51CTO博客