当前位置:Gxlcms > mysql > Mysql中那些锁机制之MyISAM_MySQL

Mysql中那些锁机制之MyISAM_MySQL

时间:2021-07-01 10:21:17 帮助过:6人阅读

说到锁机制之前,先来看看Mysql的存储引擎,毕竟不同的引擎的锁机制也随着不同。

三类常见引擎:

MyIsam 不支持事务,不支持外键,所以访问速度快。锁机制是表锁,支持全文索引

InnoDB 支持事务、支持外键,所以对比MyISAM,InnoDB的处理效率差一些,并要占更多的磁盘空间保留数据和索引。锁机制是行锁,不支持全文索引

Memory数据是存放在内存中的,默认哈希索引,非常适合存储临时数据,服务器关闭后,数据会丢失掉。

如何选择存储引擎:

MyISAM:应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高。

InnoDB用于事务处理应用程序,支持外键,如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性。更新删除等频繁(InnoDB可以有效的降低由于删除和更新导致的锁定),对于数据准确性要求比较高的,此引擎适合。

Memory通常用于更新不太频繁的小表,用以快速得到访问结果。


Mysql中的锁

如果熟悉多线程,那么对锁肯定是有概念的,锁是计算机协调多个进程或线程对某一资源并发访问的机制。

Mysql中的锁分为表锁和行锁:

顾名思义,表锁就是锁住一张表,而行锁就是锁住一行。

表锁的特点:开销小,不会产生死锁,发生锁冲突的概率高,并且并发度低。

行锁的特点:开销大,会产生死锁,发生锁冲突的概率低,并发度高。

因此MyISAM和Memory引擎采用的是表锁,而InnoDB存储引擎采用的是行锁。


MyISAM的锁机制:

分为共享读锁和独占写锁。

读锁是:当某一进程对某张表进行读操作时(select),其他线程也可以读,但是不能写。简单的理解就是,我读的时候你不能写。

写锁是:当某一进程对某种表某张表的写时(insert,update,,delete),其他线程不能写也不能读。可以理解为,我写的时候,你不能读,也不能写。

因此MyISAM的读操作和写操作,以及写操作之间是串行的!MyISAM在执行读写操作的时候会自动给表加相应的锁(也就是说不用显示的使用lock table命令),MyISAM总是一次获得SQL语句所需要的全部锁,这也是MyISAM不会出现死锁的原因。

下面分别举关于写锁和读锁的例子:

写锁:

事务1 事务2

取得first_test表的写锁:

mysql> lock table first_test write;

Query OK, 0 rows affected (0.00 sec)

当前事务对查询、更新和插入操作都可以执行

mysql> select * from first_test ;

+----+------+

| id | age |

+----+------+

| 1 | 10 |

| 2 | 11 |

| 3 | 12 |

| 4 | 13 |

+----+------+

4 rows in set (0.00 sec)

mysql> insert into first_test(age) values(14);

Query OK, 1 row affected (0.11 sec)

其他事务对锁定表的查询被阻塞,需要等到锁被释放,才可以执行

mysql> select * from first_test;

等待......

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

等待

mysql> select * from first_test;

+----+------+

| id | age |

+----+------+

| 1 | 10 |

| 2 | 11 |

| 3 | 12 |

| 4 | 13 |

| 5 | 14 |

+----+------+

5 rows in set (9 min 45.02 sec)


读锁例子如下:

事务1 事务2

获得表first_read的锁定

mysql> lock table first_test read;

Query OK, 0 rows affected (0.00 sec)

当前事务可以查询该表记录:

mysql> select * from first_test;

+----+------+

| id | age |

+----+------+

| 1 | 10 |

| 2 | 11 |

| 3 | 12 |

| 4 | 13 |

| 5 | 14 |

+----+------+

5 rows in set (0.00 sec)

其他事务也可以查到该表信息

mysql> select * from first_test;

+----+------+

| id | age |

+----+------+

| 1 | 10 |

| 2 | 11 |

| 3 | 12 |

| 4 | 13 |

| 5 | 14 |

+----+------+

5 rows in set (0.00 sec)

但是当前事务不能查询没有锁定的表:

mysql> select * from goods;

ERROR 1100 (HY000): Table 'goods'

was not locked with LOCK TABLES




其他事务可以查询或更新未锁定的表:

mysql> select * from goods;

+----+------------+------+

| id | name | num |

+----+------------+------+

| 1 | firstGoods | 11 |

| 3 | ThirdGoods | 11 |

| 4 | fourth | 11 |

+----+------------+------+

10 rows in set (0.00 sec)

而且插入更新锁定的表都会报错:

mysql> insert into first_test(age) values(15);

ERROR 1099 (HY000): Table 'first_test' was locked with

a READ lock and can't be updated

mysql> update first_test set age=100 where id =1;

ERROR 1099 (HY000): Table 'first_test' was locked with

a READ lock and can't be updated

当更新被锁定的表时会等待:

mysql> update first_test set age=100 where id =1;

等待......

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

mysql> update first_test set age=100 where id =1;

Query OK, 1 row affected (38.82 sec)

Rows matched: 1 Changed: 1 Warnings: 0



并发插入

刚说到Mysql在插入和修改的时候都是串行的,但是MyISAM也支持查询和插入的并发操作。

MyISAM中有一个系统变量concurrent_insert(默认为1),用以控制并发插入(用户在表尾插入数据)行为。

concurrent_insert为0时,不允许并发插入。

concurrent_insert为1时,如果表中没有空洞(中间没有被删除的行),MyISAM允许一个进程在读表的同时,另一个进程从表尾插入记录。

concurrent_insert为2时,无论MyISAM表中有没有空洞,都可以在末尾插入记录

事务1 事务2

mysql> lock table first_test read local;

Query OK, 0 rows affected (0.00 sec)

--加入local选项是说明,在表满足并发插入的前提下,允许在末尾插入数据

当前进程不能进行插入和更新操作

mysql> insert into first_test(age) values(15);

ERROR 1099 (HY000): Table 'first_test' was locked

with a READ lock and can't be updated

mysql> update first_test set age=200 where id =1;

ERROR 1099 (HY000): Table 'first_test' was locked

with a READ lock and can't be updated

其他进程可以进行插入,但是更新会等待:

mysql> insert into first_test(age) values(15);

Query OK, 1 row affected (0.00 sec)

mysql> update first_test set age=200 where id =2;

等待.....

当前进程不能不能访问其他进程插入的数据

mysql> select * from first_test;

+----+------+

| id | age |

+----+------+

| 1 | 100 |

| 2 | 11 |

| 3 | 12 |

| 4 | 13 |

| 5 | 14 |

| 6 | 14 |

+----+------+

6 rows in set (0.00 sec)

释放锁以后皆大欢喜

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

等待

插入的和更新的都出来的:

mysql> select * from first_test;

+----+------+

| id | age |

+----+------+

| 1 | 100 |

| 2 | 200 |

| 3 | 12 |

| 4 | 13 |

| 5 | 14 |

| 6 | 14 |

| 7 | 15 |

+----+------+

7 rows in set (0.00 sec)

mysql> update first_test set age=200 where id =2;

Query OK, 1 row affected (1 min 39.75 sec)

Rows matched: 1 Changed: 1 Warnings: 0


需要注意的:

并发插入是解决对同一表中的查询和插入的锁争用。

如果对有空洞的表进行并发插入会产生碎片,所以在空闲时可以利用optimize table命令回收因删除记录产生的空洞。

锁调度

在MyISAM中当一个进程请求某张表的读锁,而另一个进程同时也请求写锁,Mysql会先让后者获得写锁。即使读请求比写请求先到达锁等待队列,写锁也会插入到读锁之前。

因为Mysql总是认为写请求一般比读请求重要,这也就是MyISAM不太适合有大量的读写操作的应用的原因,因为大量的写请求会让查询操作很难获取到读锁,有可能永远阻塞。

处理办法:

1、指定Insert、update、delete语句的low_priority属性,降低其优先级。

2、指定启动参数low-priority-updates,使得MyISAM默认给读请求优先的权利。

3、执行命令set low_priority_updates=1,使该连接发出的请求降低。

4、指定max_write_lock_count设置一个合适的值,当写锁达到这个值后,暂时降低写请求的优先级,让读请求获取锁。

但是上面的处理办法造成的原因就是当遇到复杂的查询语句时,写请求可能很难获取到锁,这是一个很纠结的问题,所以我们一般避免使用复杂的查询语句,如果如法避免,则可以再数据库空闲阶段(深夜)执行。

人气教程排行