mysql引擎读写_mysql数据表不同引擎读写机制

为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。

一、概述

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)；BDB存储引擎采用的是页面锁(page-level

locking)，但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

二、MyISAM表锁

MyISAM存储引擎只支持表锁，是现在用得最多的存储引擎。

1、查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like ‘table%’; +———————–+———-+ | Variable_name | Value | +———————–+———-+ | Table_locks_immediate | 76939364 | | Table_locks_waited | 305089 | +———————–+———-+ 2 rows in set (0.00 sec)

如果Table_locks_waited的值比较高，说明存在着较严重的表级锁争用情况。

2、MySQL表级锁的锁模式 MySQL的表级锁有两种模式：表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁。 所以对MyISAM表进行操作，会有以下情况： a、对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。 b、对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

下面通过例子来进行验证以上观点。数据表gz_phone里有二百多万数据，字段id,phone,ua,day。现在同时用多个客户端同时对该表进行操作分析。 a、当我用客户端1进行一个比较长时间的读操作时，分别用客户端2进行读和写操作： client1: mysql>select count(*) from gz_phone group by ua; 75508 rows in set (3 min 15.87 sec)

client2: select id,phone from gz_phone limit 1000,10; +——+——-+ | id | phone | +——+——-+ | 1001 | 2222 | | 1002 | 2222 | | 1003 | 2222 | | 1004 | 2222 | | 1005 | 2222 | | 1006 | 2222 | | 1007 | 2222 | | 1008 | 2222 | | 1009 | 2222 | | 1010 | 2222 | +——+——-+ 10 rows in set (0.01 sec) mysql> update gz_phone set phone=’11111111111′ where id=1001; Query OK, 0 rows affected (2 min 57.88 sec) Rows matched: 1 Changed: 0 Warnings: 0 说明当数据表有一个读锁时，其它进程的查询操作可以马上执行，但更新操作需等待读锁释放后才会执行。

b、当用客户端1进行一个较长时间的更新操作时，用客户端2,3分别进行读写操作： client1: mysql> update gz_phone set phone=’11111111111′; Query OK, 1671823 rows affected (3 min 4.03 sec) Rows matched: 2212070 Changed: 1671823 Warnings: 0

client2: mysql> select id,phone,ua,day from gz_phone limit 10; +—-+——-+——————-+————+ | id | phone | ua | day | +—-+——-+——————-+————+ | 1 | 2222 | SonyEricssonK310c | 2007-12-19 | | 2 | 2222 | SonyEricssonK750c | 2007-12-19 | | 3 | 2222 | MAUI WAP Browser | 2007-12-19 | | 4 | 2222 | Nokia3108 | 2007-12-19 | | 5 | 2222 | LENOVO-I750 | 2007-12-19 | | 6 | 2222 | BIRD_D636 | 2007-12-19 | | 7 | 2222 | SonyEricssonS500c | 2007-12-19 | | 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 | | 9 | 2222 | NokiaN73-1 | 2007-12-19 | | 10 | 2222 | Nokia2610 | 2007-12-19 | +—-+——-+——————-+————+ 10 rows in set(2 min 58.56 sec)

client3: mysql> update gz_phone set phone=’55555′ where id=1; Query OK, 1 row affected (3 min 50.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 说明当数据表有一个写锁时，其它进程的读写操作都需等待读锁释放后才会执行。

3、并发插入原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。 a、当concurrent_insert设置为0时，不允许并发插入。 b、当concurrent_insert设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行)，MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 c、当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用 行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理系统

死锁所谓死锁: 是指两个或两个以上的进程在执行过程中, 因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去. 此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程. 表级锁不会产生死锁.所以解决死锁主要还是真对于最常用的InnoDB.

再次说明锁机制

分发注册密码的web交互程序中，普通的 ，不考虑并发的 设计如下： 设计一个表：一个字段是id，一个字段是需要分发的密码，另一个是标志位，标志该密码是否已经分发，初始是0； 程序设计：从表里找到一个标志位为0的密码，设置该标志位1，然后发给用户。 但这个方法问题是当用户的访问是高并发的时候，多个用户会得到相同的密码， 原因是(猜 想仅供参考 )： mysql的数据库操作方式是类似操作系统的读写锁，就是允许多个读锁同时操作，此时是不允许写的，当读锁放开的时候允许写，同理当写锁起作用的时候，读锁是阻塞的。所以，当用户高并发的时候，多个读锁可以一起读，第一个读锁释放后，它要将标志位置为1，但由于有其它读锁在读，所以第一个操作的写锁阻塞在这里，不能够将刚读到的这一行的标志字段，及时设置为1。并发的其他读锁读到的标志位还是0，当所有的并发读锁都释放后，所有操作的写锁开始起作用，多个并发的写操作阻塞执行，依次将该位置为1。这样多个并发的操作读的都是一条数据。 解决这个问题的方法是，仍旧利用mysql的读写锁的机制，对于这种机制，写锁一定互斥的，虽然允许同时多个读操作，但永远只允许一个写操作。刚才的问题是多个读数据的操作并发执行造成的，要避免这个，需要对读取的时候也加锁，不允许并发读取。我不知道mysql有没有这方面的设置直接来实现，但可以通过如下取巧的方式解决。 由于在写入的时候锁是互斥的，所以再建立一个表，只保存一个字段即可，就是一个自增的id，当有操作需要申请密码的时候，先在这个表里插入一条空数据，这样返回一个mysql分配的自增的id，用这个id去第一个表里取相应该id的密码就可以了。 不会出现 多个用户得到同样密码的 解释是，此时多个并发的操作肯定可以得到不同的id，因为在插入的时候写锁是互斥的，并发的多个操作要想写数据库，就会阻塞排队，第一个操作写入后，释放了该锁，获得 mysql分配的id，其后的操作需要执行insert操作，mysql就会将这个操作顺序插入数据库的不同行，返回不同的id，此时虽然操作是并发的，同时到达的，但对于mysql来说，是一条一条执行插入语句，所以当然操作的是不同的行，返回不同的id，这样在第一个表里找到的就是不同的密码，用户分配到的也是不同的密码。 当这个分配的id大于密码表里的id总数时候，表示密码全部发送完。