MySQL优化4之锁

最新推荐文章于 2024-10-02 12:42:27 发布

HappyWealthy

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量91

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_43525326/article/details/128940033

版权

1、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。数据库中的数据属于共享资源，为保证数据并发访问的一致性、有效性，需要给表或者表中的记录加锁。

锁的分类

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

加锁sql

lock table 表名字 read(write), 表名字2 read(write), 其他;

查看表上锁的sql

show open tables;

释放锁的sql

unlock tables;

2、读锁

创建表并且给表加读锁

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

lock table mylock read;

给mylock表添加写锁

lock tables mylock write;

3、再述MyISAM

MyISAM存储引擎在执行查询语句前，会自动给涉及的表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表锁有两种模式

表共享读锁
表独占写锁

对于MyISAM存储引擎中的表进行操作，会有如下两种情况

对MyISAM表的进行读操作（加读锁），不会阻塞其他进程对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才会执行其它进程的写操作。
对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

3.1、表锁分析

mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 21    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

table_locks_waited：表加锁后，其他进程为了访问和操作表而产生的等待数(不能立即获取锁的次数，每等待一次锁值加1)

table_locks_immediat：表示可以立即获取锁的查询次数，每立即获取锁值加1 ;

MyISAM的读写锁调度是写优先，所以MyISAM不适合做以读为主的表的存储引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

4、行锁

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

4.1、行锁分析

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

对各个状态量的说明如下:

状态量	说明	其他
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度	重要
Innodb_row_lock_time_avg	每次等待所花平均时间	重要
Innodb_row_lock_time_max	从系统启动到现在等待最常的一次所花的时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数	重要