MySQL心得10

1. 伪事务 前面已经介绍了InnoDB和BDB表环境中的事务，这是MySQL天生支持的ACID规则事务的仅有的表类型。旧的MySQL表类型，在许多安装MySQL的环境中仍然在使用，它们不支持事务，但是MySQL仍然可以使用户通过表锁定来实现原始形式的事务。在此介绍伪事务，提

1. 伪事务

前面已经介绍了InnoDB和BDB表环境中的事务，这是MySQL天生支持的ACID规则事务的仅有的表类型。旧的MySQL表类型，在许多安装MySQL的环境中仍然在使用，它们不支持事务，但是MySQL仍然可以使用户通过表锁定来实现原始形式的事务。在此介绍伪事务，提供对非事务表执行安全事务的一些基本指标。

2. 锁定与解锁

因为MyISAM（以及其他旧的MySQL表）不支持InnoDB格式的COMMIT和ROLLBACK语法，所以每次数据库的变化都被立即保存在磁盘上。像前面所讲的那样，在单用户的环境中，这没有问题，但是在多用户的环境中，就会导致很多问题。因为它不能创建事务来使用户所做的变化隔离于其他用户所做的变化。在这种情况下，唯一一种保证不同用户能够看到一致数据的方法是强制方法：在变化的过程中阻止其他用户访问正在变化的表（通过锁定表），只在变化完成后才允许访问。

前面已经讨论了InnoDB和BDB表，它们支持行级和页级的锁定来保证同时执行的事务的安全。不过，MyISAM表类型不支持这些锁定机制。所以，要明确地设置表锁定，以避免同时存在的事务互相侵犯空间。

MySQL提供了LOCK TABLES语句来锁定当前线程的表，语法格式如下：LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

说明：

表锁定支持以下类型的锁定。

● READ：读锁定，确保用户可以读取表，但是不能修改表。加上LOCAL后允许在表锁定后用户可以进行非冲突的INSERT语句，只适用于MyISAM类型的表。

● WRITE：写锁定，只有锁定该表的用户可以修改表，其他用户无法访问该表。加上LOW_PRIORITY后允许其他用户读取表，但是不能修改它。

当用户在一次查询中多次使用到一个锁定了的表，需要在锁定表的时候用AS子句为表定义一个别名，alias表示表的别名。

3. 表锁定只用于防止其他客户端进行不正当地读取和写入。

保持锁定（即使是读取锁定）的客户端可以进行表层级的操作，如DROP TABLE。

在对一个事务表使用表锁定的时候需要注意以下几点：

（1）在锁定表时会隐式地提交所有事务，在开始一个事务时，如START TRANSACTION，会隐式解开所有表锁定。

（2）在事务表中，系统变量AUTOCOMMIT值必须设为0。否则，MySQL会在调用LOCK TABLES之后立刻释放表锁定，并且很容易形成死锁。

例： 在XS表上设置一个只读锁定：LOCK TABLES XS READ;

说明：LOCK TABLES还可以同时锁定多个表，中间用逗号隔开即可。

例: 在KC表上设置一个写锁定: LOCK TABLES KC WRITE;

在锁定表以后，可以使用UNLOCK TABLES命令解除锁定。语法格式为: UNLOCK TABLES;

UNLOCK TABLES命令不需要指出解除锁定的表的名字。MySQL会自动对前面通过LOCK TABLES锁定的所有表解除锁定。当用户发布另一个LOCK TABLES时，或当与服务器的连接关闭时，所有由当前用户锁定的表被隐式地解锁。

4.当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定，它是实现数据库并发控制的主要手段。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。具体地说，锁定可以防止丢失更新、脏读、不可重复读和幻读。

丢失更新（lost update），指当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，因此最后的更新将重写由其他事务所做的更新，这将导致数据丢失。

脏读（dirty read）指一个事务正在访问数据，而其他事务正在更新该数据，但尚未提交，此时就会发生脏读问题，即第一个事务所读取的数据是“脏”（不正确）数据，它可能会引起错误。

当一个事务多次访问同一行而且每次读取不同的数据时，会发生不可重复读（unrepeatable read）问题。不可重复读与脏读有相似之处，因为该事务也是正在读取其他事务正在更改的数据。当一个事务访问数据时，另外的事务也访问该数据并对其进行修改，因此就发生了由于第二个事务对数据的修改而导致第一个事务两次读到的数据不一样的情况，这就是不可重复读。

当一个事务对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读（phantom read）问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次读或后续读显示有一行已不存在于原始读中。