第一章 MYSQL的架构和历史

在读第一章的过程中,整理出来了一些重要的概念.

锁粒度

表锁(服务器实现,忽略存储引擎).

行锁(存储引擎实现,服务器没有实现).

事务的ACID概念

原子性(要么全部成功,要么全部回滚).

一致性(从一个一致性状态转换到另外一个一致性状态).

隔离性(一个事务所做的修改在提交前,对其他事务是不可见的).

持久性(一旦事务提交,所有修改都会永久保存到数据库中).

四种隔离级别

READ UNCOMMITTED(未提交读): 事务即使没有提交,所做的修改对其他事务是可见的. 也称脏读.

READ COMMITTED(提交读):　事务没有提交前,所做的任何修改对其他事务是不可见的.这大多数数据库默认的隔离级别,也叫不可重复读.

REPEATABLE READ(可重复读): 当某个事务读取某个范围内记录时,另外一个事务又在该范围内插入了新纪录并且提交成功,这时前一个事务再次读取该范围内记录,便会产生幻行.

SERIALIIZABLE(可串行化): 通过强制事务串行执行,避免幻读的问题, 也就是在读取的每一行记录上加锁,所以可能导致大量超时和锁争用,实际中很少用这个隔离级别.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

设置隔离级别,在下次事务时生效.

死锁

多个事务在同一资源相互占用,并请求锁定对方占用的资源,从而导致恶性循环. 当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁.多个事务同时锁定同一资源,也会产生死锁.

Transaction #1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;
Transaction #2
START TRANSACTION;
UPDATE StockPrice SET high  = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high  = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT

上面两个事务同时执行了第一条语句,更新了一条记录,同时都锁定该条记录,接着事务执行第二条记录,发现被锁定,于是都等待对方释放锁这时就陷入了死循环.

解决:

当查询等待的时间超过锁等待超时的设定后就会放弃锁请求.

InnoDB处理: 将持有最少行级排他锁进行回滚.

自动提交

默认采用自动提交(AUTOCOMMIT), 如果不显示开始一个事务,则每次查询都被当作一个事务执行提交操

SHOW VARIABLES LIKE 'AUTOCOMMIT'

1或者ON表示启用,0或者OFF表示禁用

SET AUTOCOMMIT = 1; 开启自动提交

修改AUTOCOMMIT对非事务型的表,比如MYISAM或者内存表没有任何影响.

同一事务中使用多种存储引擎(InnoDB和MYISAM)不可靠,正常情况下不会有什么问题,但如果该事务需要回滚,则非事务型表上的操作不可撤销.

多版本并发控制

MVVC是行级锁的一个变种,但它在很多情况下避免了加锁的操作,因此开销更低.

InnoDB简化版的MVCC:通过在每行记录保存两个隐藏的列,一列保存行的创建时间, 一列保存行的过期时间(删除时间) 当然不是实际时间,而是系统版本号. 每次开始一个事务,系统版本号就会递增, 事务开始时刻的系统版本号作为事务版本号.

SELECT

　　a. InnoDB只查找版本早于当前事务版本号的数据行(也就是, 行的版本号小于等于事务的系统版本号), 这样保证事务读取的行,要么是在事务开始就已经存在,要么是事务自身插入或修改过的.

　　B.行的删除版本要么未定义,要么大于事务版本号,这样确保事务读取的行在事务之前没有被删除。

　　只有符合这两个条件的记录,才能作为查询结果.　　

INSERT

　　InnoDB为新插入的每一行记录保存当前版本号作为行版本号.

DELETE

　　InnoDB为删除的每一行保存当前系统版本号作为删除标识.

UPDATE

　　InnoDB为插入一行新纪录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识.

只有在REPEATABLE READ 和 READ COMMITED 两个隔离级别工作,其他隔离级别都和MVCC不兼容, 应为READ UNCOMMITTED总是读取最新记录行,不符合当前事务版本的数据行. 而SERIALIIZABLE会为读取到的行加锁.