大佬教程收集整理的这篇文章主要介绍了MySQL学习之路(一)锁机制,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
表锁
:锁住整张表行锁
:锁住某行表记录间隙锁
:锁住某个区间行记录偏向@H_238_6@myISAM引擎,开销小,加锁快;无死锁;锁粒度大,发生锁冲突的概率最高,并发度最低
create table csde_myisam (
`id` VARCHAR(64),
`user_name` VARCHAR(512) NOT NULL,
`password` VARCHAR(256),
`display_name` VARCHAR(128),
PRIMARY KEY (`id`))ENGINE myisam;
INSERT INTO csde_myisam
(id, user_name, password, display_Name)
VALUES
('1', 'Kai', '123', 'wuKai'),
('2', 'jay', '123', 'jayy'),
('3', 'beasyer', '123', 'beasyer liu');
SHOW OPEN TABLES;
In_use
@H_238_6@mysql> SHOW OPEN TABLES; +--------------------+---------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+---------------------------+--------+-------------+ | mysql | index_stats | 0 | 0 | .... | mysql | gtid_executed | 0 | 0 | | information_scheR_500_11845@a | SHOW_STATISTICS | 0 | 0 | | mysql | component | 0 | 0 | | mysql | columns | 0 | 0 | | Kaiwu3 | csde_myisam | 1 | 0 | | mysql | func | 0 | 0 | | information_scheR_500_11845@a | columNS | 0 | 0 | | mysql | events | 0 | 0 | | mysql | catalogs | 0 | 0 | | Kaiwu3 | csde | 0 | 0 | | mysql | collations | 0 | 0 | | mysql | table_partitions | 0 | 0 | | information_scheR_500_11845@a | TABLES | 0 | 0 | | mysql | time_zone_transition_type | 0 | 0 | | mysql | tablespaces | 0 | 0 | +--------------------+---------------------------+--------+-------------+ 54 rows in set (0.00 seC)
LOCK TABLE table_name READ/WRITE, table_name2 READ/WRITE;
UNLOCK TABLES;
# 给csde_myisam这张表加读锁==>myisam只支持表锁,不支持行锁
LOCK TABLE csde_myisam READ, csde WRITE;
Session-1 | Session-2 |
@H_859_94@mysql> lock table csde_myisam read;
+----+-------------+------------+-----------------+
+----+-------------+------------+-----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+------------+-----------------+
ERROR 1100 (HY000): Table 'csde' was not locked with LOCK TABLES
当前csde_myisam表被锁,释放后当前session才可以操作其他表
ERROR 1099 (HY000): Table 'csde_myisam' was locked with a READ lock and can't be updated
@H_853_122@mysql> unlock tables;
当前session对锁定表可读,不可写,不可读其他表
|
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 1 | Kai | 123 | wuka |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
blocking....
当前session对锁定表可读,写阻塞,可读其他表
|
分析:写操作(update, insert, delete)都会自动添加写锁(排它锁)。
Session-1 | Session-2 |
@H_853_122@mysql> lock table csde_myisam write;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
ERROR 1100 (HY000): Table 'csde' was not locked with LOCK TABLES
当前csde_myisam表被锁,释放后当前session才可以操作其他表
@H_853_122@mysql> unlock tables;
当前session对锁定表可读,可写,不可读其他表
|
blocking...
==>取消
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
blocking....
当前session对锁定表不可读,不可写,对其他表可读写
|
@H_238_6@myISAM在执行SELEct语句前,会自动给涉及的表加读锁,执行写操作之前,会自动给涉及的表加写锁。
对MyISAM的读操作(加读锁)
:不会阻塞其他Session对同一表的读操作,但是会阻塞其他Session的写操作,直到读锁释放。对MyISAM的写操作(加写锁)
:会阻塞其他Session对同一表的读写操作,直到写锁释放。InnodB引擎
,开销大,加锁慢;会出现死锁;锁粒度最小,发生锁冲突的概率最低,并发度也最高。InnoDB支持事务,行锁
,@H_238_6@myISAM不支持事务,只支持表锁。索引字段
过滤,对非索引字段
进行过滤,行锁会失效升级为表锁
。注意:针对索引字段进行过滤时,如果索引失效
,同样会将行锁升级为表锁
(开发巨坑,勿踩)。create table csde_innodb (
`id` VARCHAR(64),
`user_name` VARCHAR(512) NOT NULL,
`password` VARCHAR(256),
`display_name` VARCHAR(128),
PRIMARY KEY (`id`))ENGINE INNODB;
INSERT INTO csde_innodb
(id, user_name, password, display_Name)
VALUES
('1', 'Kai', '123', 'wuKai'),
('2', 'jay', '123', 'jayy'),
('3', 'beasyer', '123', 'beasyer liu');
alter table csde_innodb ADD INDEX idx_user_name(user_name);
";"
会自动提交,关闭自动提交后,必须手动输入“commit;”
才会真正提交索引字段
时针对该索引字段添加行锁。这里关闭自动提交,主要用于方便debugSET autocommit=0; # 每个session需单独设置
索引字段
)索引字段
)SELECT * FROM csde_innodb WHERE id=4 FOR updatE;
关于事务隔离,后续会单独再写一篇博客,这里就不详细介绍了。
@H_238_6@mysql> SELECT @@global.transaction_isolation; +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | REPEATABLE-READ | +--------------------------------+ mysql> SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | REPEATABLE-READ | +---------------------------------+
Session-1 | Session-2 |
SELEct * from csde_inndob;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
SELEct * from csde_innodb;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
commit;
commit;
SELEct * from csde_innodb;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
commit;
SELEct * from csde_innodb;
|
SELEct * from innodb
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
SELEct * from csde_innodb;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
*没有提交,还在上个事务中,可重复读
commit;
SELEct * from csde_innodb;
+----+-------------+-----------+----------------+
+----+-------------+-----------+----------------+
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
blacking...
Query OK...
commit;
|
前面我们说过,要想行锁生效,过滤条件(where)一定要加在索引字段
上,否则将升级为表锁。但是,在实际开发过程中,由于虽然过滤条件为索引字段,但真正的查询不一定是走索引的--索引失效
,此时行锁同样会升级为表锁。
查询csde_innodb这张表,存在两个单键索引
(PriMary(id), idx_user_name(user_name)
@H_238_6@mysql> show index from csde_innodb; +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | NON_UNIQUE | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | Index_comment | Visible | Expression | +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | csde_innodb | 0 | PRIMary | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | csde_innodb | 1 | idx_user_name | 1 | user_name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Session-1 | Session-2 |
老开发应该都非常熟悉,并非针对索引行过滤就一定会生效,某些场景下,即使针对索引过滤
,依旧会存在索引不生效
的案例。这里简单介绍几种索引失效场景:
关于索引失效或者优化问题,后续会单独再出一篇文章,这里就不详细展开介绍了。
Session-1 | Session-2 |
@H_238_6@mysql> SHOW STATUS LIKE 'innodb_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 1 | | Innodb_row_lock_time | 482516 | | Innodb_row_lock_time_avg | 10489 | | Innodb_row_lock_time_max | 51036 | | Innodb_row_lock_waits | 46 | +-------------------------------+--------+
Innodb_row_lock_current_waits
: 当前正在锁定等待的数量Innodb_row_lock_time
: 从系统启动到现在锁定的总时间Innodb_row_lock_time_avg
:每次等待花费的平均时间Innodb_row_lock_time_max
: 锁定等待花费的最长时间Innodb_row_lock_waits
:从系统启动到现在锁定等待的总次数当数据库的总等待时间比较长,平均等待时间比较高,等待锁定的总次数比较时,应该排查系统,进行相应的优化。
间隙锁(Next-Key)
: 针对某个索引字段,锁定一定范围的行记录。
范围
时,Mysql自动给该范围的行记录添加间隙锁;可重复读
(Mysql默认)隔离级别下,普通索引
字段添加行锁时自动升级为间隙锁
,间隙为上下两条表记录(左闭右开区间
),主键索引
添加行锁时则为普通行锁
。@H_238_6@mysql> alter table csde_innodb CHANGE columN id id integer(74) NOT NULL; mysql> alter table csde_innodb CHANGE columN password password IntegeR(64); mysql> alter table csde_innodb ADD columN number IntegeR(64);
@H_238_6@mysql> DESC csde_innodb; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | user_name | varchar(256) | NO | MUL | NULL | | | password | int | YES | | NULL | | | display_name | varchar(128) | YES | | NULL | | | number | int | YES | MUL | NULL | | +--------------+--------------+------+-----+---------+-------+
@H_238_6@mysql> alter table csde_innodb ADD INDEX idx_numer(number);
@H_238_6@mysql> SHOW INDEX FROM csde_innodb; +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | NON_UNIQUE | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | Index_comment | Visible | Expression | +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | csde_innodb | 0 | PRIMary | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | csde_innodb | 1 | idx_user_name | 1 | user_name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | csde_innodb | 1 | idx_numer | 1 | number | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
默认隔离级别
,根据普通索引
字段过滤,针对某一行记录进行写操作或者主动添加行锁,将自动升级为间隙锁
,间隙为排序后上下表记录左闭右开区间
。
Session-1 | Session-2 |
+----+-------------+-----------+----------------+----------+
+----+-------------+-----------+----------------+----------+
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
+----+-------------+-----------+----------------+----------+
自动添加间隙锁[3,7)
commit;(间隙锁锁移除)
|
+----+-------------+-----------+----------------+----------+
+----+-------------+-----------+----------------+----------+
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
+----+-------------+-----------+----------------+----------+
@H_853_122@mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 3);
Blocking...
@H_853_122@mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 6);
Blocking...
@H_853_122@mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 7);
Query OK...
@H_853_122@mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(6, 'bevis', 100, 'bevis duan', 2);
Query OK...
|
默认隔离级别
下,根据主键索引
字段过滤,对某一行记录进行写操作或者主动添加行锁,仅添加普通的行锁
。
Session-1 | Session-2 |
+----+-------------+-----------+----------------+----------+
+----+-------------+-----------+----------------+----------+
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
| 7 | bevis | 100 | bevis duan | 3 |
+----+-------------+-----------+-----------------+---------+
自动添加行锁
commit;(行锁移除)
|
+----+-------------+-----------+----------------+----------+
+----+-------------+-----------+----------------+----------+
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
| 7 | bevis | 100 | bevis duan | 3 |
+----+-------------+-----------+-----------------+---------+
@H_853_122@mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 3);
Query OK...(不存在间隙锁)
Blocking...(存在行锁)
Query OK...
|
索引字段作为过滤条件
,防止行锁升级为表锁;缩小锁的范围
避免使用范围检索
,减小间隙锁的锁定范围控制事务的大小
,减少锁定资源的时间和范围作者:吴家二少 博客地址:博客园 本文欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接
以上是大佬教程为你收集整理的MySQL学习之路(一)锁机制全部内容,希望文章能够帮你解决MySQL学习之路(一)锁机制所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。