mysql 锁机制

2022/1/24 19:10:11

本文主要是介绍mysql 锁机制,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

锁是为了保护某一个资源同时被多个进程或线程修改,从而造成数据混乱。
锁粒度:
  表级锁:对整张表加锁,开销小,加锁快,不会出现死锁,锁粒度大,锁竞争概率高,并发效率低
  行级锁:对某行主键索引,开销大,加锁慢,会出现死锁,锁粒度小,锁竞争概率低,并发效率高
  运用场景不同,选择合理锁,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据
  MYISAM和MEMORY存储引擎采用的是表级锁,InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁
MYISAM表级锁
  当一个线程获取到表的读锁时,会阻塞其他用户对该表的写操作,但是不会阻塞其他用户对该表读操作,阻塞写操作,当一个线程获取到表的写锁时,会阻塞其他用户的读写操作,正常情况是自动加锁。

测试数据:
CREATE TABLE `mylock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `mylock` VALUES (1, 'zhangsan'),(2, 'lisi');
commit;

  表独占写锁:

--打开两个mysql窗口A,B
--A窗口给mylock加写锁
A:lock table mylock write;
A:select * from mylock;
A:insert into mylock values (3,'wangwu');
B:select * from mylock; -- 出现阻塞
A:unlock tables; --释放锁

  表共享读锁:

--A窗口给mylock加读锁
A:lock table mylock read;
A:select * from mylock;
A:select * from test_trancation; --报错,查询其他未加锁表
B:select * from mylock;
B:insert into mylock values (4,'cuihua');--阻塞
A:insert into mylock values (5,'xiaozhu');--报错,当前为读锁
A:unlock tables;
B:执行成功

  查询表级锁争用情况:

show status like 'table_locks%';

InnoDB行级锁和表级锁:
  
通过给主键加锁,如果是通过其他索引访问,其他索引存储的也是主键值,未使用索引访问,则是会使用表级锁,即使使用索引也有可能加表级锁。mysql优化器认为表级锁效率更高时。
  验证表级锁:

测试数据
CREATE TABLE `tab_no_index`(
`id` int,
`name` varchar(10)
) engine=innodb;
insert into tab_no_index values(1,'zhangsan'),(2,'lisi');
COMMIT;
--打开两个mysql窗口A,B
A:set autocommit=0;
A:select * from tab_no_index where id = 1;
B:set autocommit=0
B:select * from tab_no_index where id =2;
A:select * from tab_no_index where id = 1 for update;
B:select * from tab_no_index where id = 2 for update;--出现阻塞等待
alter table mylock add PRIMARY KEY (`id`);

  共享锁(S锁):允许多个事务去读一行,阻止事务获取排他锁,加锁的方式:SELECT .. LOCK IN SHARE MODE
  排他锁(X锁):允许一个事务获取排他锁,阻止其他事务获取共享锁和排他锁,加锁的方式:SELECT .. FOR UPDATE(INSERT,DELETE)
  间隙锁:是对锁的补充定义,对于符合加锁条件的数据,在完锁后,查询或修改的数据,使其满足条件,重新加锁,可以防止幻读。
  查询锁竞争情况:

show status like 'innodb_row_lock%';

MVCC多版本并发控制:
  是在读已提交(生成语句级快照)和可重复读隔离级别(生成事务级快照)下起作用,在读取数据的时候生成版本号,等到其他事务commit之后,产生新的版本号,当事务A读取了记录(生成版本号),事务B修改记录,事务A再次读取时,会根据最新的版本号读取,如果事务B还没有提交,事务A读取之前的版本号数据。
  主要是通过read view 和undo log来实现的,undo log记录了事务前的数据,而read view是在查询时,InnoDB会生成一个read view,read view有几个重要字段,分别是:trx_ids(尚未提交的事务版本号集合),low_limit_id(下一次要生成的事务ID值),low_limit_id(尚未提交版本号的事务ID最小值)以及creator_trx_id(当前的事务版本号),在每行数据有两列隐藏的字段,分别是DB_TRX_ID(记录着当前ID)以及DB_ROLL_PTR(指向上一个版本数据在undo log 里的位置指针),read commit隔离级别下,每次都获取一个新的read view,repeatable read隔离级别则每次事务只获取一个read view。



















这篇关于mysql 锁机制的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程