Mysql中那些锁机制之MyISAM
来源:互联网 发布:sql中怎么表示至少 编辑:程序博客网 时间:2024/06/06 04:30
说到锁机制之前,先来看看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表中有没有空洞,都可以在末尾插入记录
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设置一个合适的值,当写锁达到这个值后,暂时降低写请求的优先级,让读请求获取锁。
但是上面的处理办法造成的原因就是当遇到复杂的查询语句时,写请求可能很难获取到锁,这是一个很纠结的问题,所以我们一般避免使用复杂的查询语句,如果如法避免,则可以再数据库空闲阶段(深夜)执行。
参考:
深入浅出Mysql数据库开发、优化与管理维护.第二版
- Mysql中那些锁机制之MyISAM
- Mysql中那些锁机制之InnoDB
- Mysql中那些锁机制之InnoDB
- Mysql中那些锁机制之InnoDB
- Mysql中那些锁机制之InnoDB
- Mysql中那些锁机制之InnoDB
- Mysql中那些锁机制之InnoDB
- 【MySQL锁之】MyISAM存储引擎及其锁机制
- mysql中Myisam锁问题
- MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
- MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
- MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解
- 关于mysql中myisam中表级锁
- MySQL 之 MyISAM存储引擎
- Mysql引擎之MyISAM引擎
- mysql之MyISAM和InnoDB
- MySQL中MyISAM引擎优化
- 【MySQL】MySQL存储引擎MyISAM与Innodb的那些事
- LotusScript捕捉错误
- JavaScript基础----42JavaScript惰性函数(没写代码)
- JavaScript基础----43函数柯里化(没理解)
- Java与其他语言的对比
- JavaScript基础----44JavaScript级联函数
- Mysql中那些锁机制之MyISAM
- [数据结构]最小生成树
- 《机器学习系统设计》之数据理解和提炼
- 应用程序无法正常启动0xc000007b
- JavaScript基础----45JavaScript正则表达式
- Hbase总结(四)- Hbase与传统数据库的区别
- javascript立即调用函数表达式
- 剑指Offer面试题32(java版):从1到n整数中1出现的次数
- MyEclipse10安装SVN插件