Home > Database > Mysql Tutorial > Mysql中那些锁机制之MyISAM_MySQL

Mysql中那些锁机制之MyISAM_MySQL

WBOY
Release: 2016-06-01 12:58:51
Original
1097 people have browsed it

说到锁机制之前,先来看看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设置一个合适的值,当写锁达到这个值后,暂时降低写请求的优先级,让读请求获取锁。

 

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


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template