未加星标

mysql的myisam引擎锁问题

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
一,概述

相对其他数据库而言,mysql的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如:myisam和memory存储引擎采用的是表级锁,bdb采用的是页面锁,但也支持表级锁,innodb存储引擎即支持行级锁也支持表级锁,但默认情况下是行级锁。

三种锁的特性大致归纳如下:

(1)表级锁:开销小,加锁快;不会出现死锁;锁粒度大,发生冲突的概率高,并发度低。

(2)行级锁:开销大,加锁慢;会出现锁死;锁粒度最小,发生锁冲突的概率最低,并发度也高。

(3)页面锁: 开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。

所以,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用。而行级锁则更适合于有大量按照索引条件并发更新少量不同数据,同时又有并发查询的应用。

二,myisam表锁

1,通过查看状态变量来分析系统上表锁定争夺

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

以上两个数据,如果table_locks_waited的值更高,则说明存在较严重的表级锁争用情况。

2,表级锁的锁模式

mysql的表级锁有两种模式:表共享读锁(table read lock)和表独占写锁(table write lock)。锁模式的兼容性如下:


mysql表锁兼容性
none
读锁
写锁
读锁



写锁



可见,对myisam表的读操作不会阻塞其他用户对同一表的读操作,但会阻塞对同一表的写操作.

对myisam表的写操作则会阻塞其他用户对同一表的读和写操作。

myisam表的读操作与写操作之间,以及写操作之间是串行的。

下面我们来看一个例子:

首先,我们创建表并插入几条数据,看一下表的结构:

mysql> create table t18(id int auto_increment primary key,name varchar(63),age int(3));
Query OK, 0 rows affected (0.33 sec)
mysql> insert into t18(name,age)values('fzy1',1),('fzy2',2),('fzy3',3);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 2 |
| 3 | fzy3 | 3 |
+----+------+------+
3 rows in set (0.00 sec)
写阻塞读例子
session_1
session_2
获得表t18的write锁
mysql> lock table t18 write;
Query OK, 0 rows affected (0.00 sec)
当前session对锁定的表的查询,更新,插入操作都可以执行
mysql> insert into t18(name,age)values('fzy4',4);
Query OK, 1 row affected (0.04 sec)
mysql> update t18 set age=5 where id=2;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
其他session对锁定的表操作被阻塞
mysql> select * from t18;
等待
释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
session2获得锁,返回查询结果
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (1 min 21.56 sec)
读阻塞写例子
session_1
session_2
获得t18的read锁定
mysql> lock table t18 read;
Query OK, 0 rows affected (0.00 sec)
当前session可以查询该表记录
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
session_2也可以获得该表记录
mysql> select * from t18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | fzy1 | 1 |
| 2 | fzy2 | 5 |
| 3 | fzy3 | 3 |
| 4 | fzy4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
当前session不能查询未锁定的表,但可以看锁定的表(这个会话中锁定的表)
mysql> select * from t17;
ERROR 1100 (HY000): Table 't17' was not locked with LOCK TABLES
其他session可以查询和更新未锁定的表
mysql> select * from t17;
+----+------+------+--------+
| id | name | age | intro |
+----+------+------+--------+
| 1 | fzy1 | 1 | intro1 |
| 2 | fzy2 | 2 | intro2 |
| 3 | fzy3 | 3 | intro3 |
| 4 | hsq | 4 | intro4 |
+----+------+------+--------+
4 rows in set (0.00 sec)
当前session中插入或更新数据都会报错
mysql> insert into t18(name,age)values('fzy5',5);
ERROR 1100 (HY000): Table 't18' was not locked with LOCK TABLES
其他session更新锁定的表会等待获得锁
mysql> insert into t18(name,age)values('fzy6',6);
等待
释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
获得锁执行
Query OK, 1 row affected (2 min 33.25 sec)

当多次使用lock tables时,不仅需要一次锁定用到的所有表,而且,同一个表在sql语句中出现多少次,就要通过与sql语句中相同的别名来锁定多少次,否则也会出错。

例子:

mysql> lock table t18 read;
Query OK, 0 rows affected (0.00 sec)
mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES
我们做了一个表本身的连接,查询出错,正确的做法如下:
##先释放上面的锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
##对别名分别锁定
mysql> lock table t18 a read,t18 b read;
Query OK, 0 rows affected (0.00 sec)
mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
+------+------+
| name | name |
+------+------+
| fzy1 | fzy1 |
| fzy2 | fzy2 |
| fzy3 | fzy3 |
| fzy4 | fzy4 |
| fzy6 | fzy6 |
| fzy6 | fzy6 |
+------+------+
6 rows in set (0.01 sec)

3,如何加表锁

myisam在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行更新操作(update,delete,insert)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用lock table命令个myisam表显式加锁。这边我们加锁是为了模拟事务操作,实现对某一时间点多个表的一致性读取。

当我们需要同时对两张表锁定的时候:

mysql> lock tables t17 read local,t18 read local;
Query OK, 0 rows affected (0.00 sec)
#执行操作
...
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(1)上面的例子lock tables时加了local 其作用就是在满足myisam表并发插入的条件情况下,允许其他用户在表尾并发插入记录

(2)在用lock tables给表显式加表锁的时候,必须同时取得所有涉及表的锁,并且mysql不支持锁升级,也就是说,在执行lock tables后只能访问显式加锁的这些表,不能访问未加锁的表。在自动加锁的情况下,myisam总是一次读取sql语句所需要的全部锁,这也是myisam不会出现死锁的原因。

4,并发插入

myisam表的读和写是串行的,但这是就总体而言的,在一定条件下,myisam表也支持查询和插入操作的并发。

myisam存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值可以分别为0,1,2:

当值为0时,不允许并发插入,

当值为1时,如果myisam表中没有空洞(即表的中间没有被删除的行),myisam允许在一个进程读表的同时,另一个进程从表尾插入记录,这也是mysql的默认设置,

当值为2时,无论myisam表中有没有空洞,都允许在表尾并发插入记录。

5,myisam的锁的调度

myisam存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个myisam表的读锁,同时另一个进程也请求同一表的写锁,mysql怎么处理呢?

答案是写进程先获得锁,不仅如此,即使是读请求先到锁等待队列,写请求后到,写锁也会插入到读锁之前。这是因为mysql认为写请求一般比读请求更重要。不过我们可以通过一些设置来调节myisam的调度行为。

(1)通过指定启动参数low-priority-updates,使myisam引擎默认给予读请求以优先的权利

(2)通过执行命令set low_priority_updates=1,使该连接发出的更新请求优先级降低

(3)通过指定insert ,update ,delete 语句的low_priority属性,降低该语句的优先级。

三,小结

好,下面对锁问题总结一下:主要介绍了mysql中myisam表级锁实现特点:

对于myisam的表锁,主要有一下几点:

(1)共享锁(s)之间是兼容的,但共享锁(s)与排它锁(x)之间,以及排它锁写锁(x)之间是互斥的,也就是说读和写是串行的。

(2)在一定条件下,myisqm允许查询和插入并发执行,可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

(3)myisam默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以设置low_priority_updates参数,或在insert ,update ,delete 语句中指定 low_prority选项来调节读写锁的争用。

(4)由于表锁的锁定粒度打,读写之间又是串行的,因此,如果更新操作较多,myisqm表可能会出现严重的锁等待,可以考虑采用innodb表来减少冲突。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: 数据冲突删除变量数据库
分页:12
转载请注明
本文标题:mysql的myisam引擎锁问题
本站链接:http://www.codesec.net/view/483008.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(43)