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

我会带着你远行 2022-08-03 16:34 132阅读 0赞

为了给高并发情况下的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状态变量来分析系统上的表锁定争夺：

如果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应用  
行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理系统

**死锁**  
所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,  
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.  
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.  
表级锁不会产生死锁.所以解决死锁主要还是真对于最常用的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总数时候，表示密码全部发送完。