mysql事务隔离级别以及悲观锁-乐观锁

事务概念：
一组mysql语句，要做的话全都做完。如果没有做完，把之前做的也撤回
事物特点：

原子性
稳定性：有非法数据(如外键约束)，事物撤回
隔离性：事务独立运行。对其他事务的影响也保证原子性。
可靠性: 日志保存事务记录，以防软硬件崩溃之后可以恢复之前的事务操作

以上不是重点，重点是对事务控制语句不熟悉。

事务控制语句
BEGIN 或者 START TRANSACTION: 显示的开启一个事务。
事务执行的结果有两个：
1）COMMIT: 提交事务，并将记录修改写入数据库。
2）ROLLBACK: 回滚事务，并撤销 未COMMIT的修改。

SAVEPOINT identifier: 在事务中创建保存点。一个事务中允许有多个保存点。
RELEASE SAVEPOINT identifier: 删除保存点。当事务中没有指定的保存点，执行该语句会抛异常。
ROLLBACK TO identifier: 把事务回滚到保存点。

mysql中的 `事务隔离级别`

Read Uncommited
A, B两个事务， A可以看到 B未提交的执行语句的结果。也被称为(Dirty Read).
Read Commited
A, B两事务，B只能看见 A已经提交事务所做的改变。
该隔离级别是大多数数据库默认的事务隔离级别(但不是mysql的)。
Repeatable Read
A, B两事务，A已经提交事务，但是B还是看不到 A事务所做的变更(B看到的还是A事务未提交之前的状态)。该隔离级别会导致新的问题(Phantom Read幻读: )。mysql的默认隔离级别。
Serializable
对所要读的数据记录加上锁，使得所有对该数据记录的操作都不得不排队。该级别解决了幻读的问题，但会导致锁的竞争。
对后面两种隔离级别还是不大懂。

Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.

Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.

The next isolation level, serializable, makes an even stronger guarantee: in addition to everything repeatable read guarantees, it also guarantees that no new data can be seen by a subsequent read.

Say you have a table T with a column C with one row in it, say it has the value '1'. And consider you have a simple task like following:

BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
SELECT * FROM T;
COMMIT;

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

under READ COMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
under REPEATABLE READ the second SELECT is guaranteed to see the rows that has seen at first select unchanged. New rows may be added by a concurrent transaction in that one minute, and the existing rows can be deleted or changed because repeatable read simply reads a "snapshot" not the actual data. From the docs dev.mysql.com/doc/refman/5.0/en/…: "All consistent reads within the same transaction read the snapshot established by the first read. The reads will still see the 'old' original data as if the change had not taken place (the snapshot)."
Yes, Phantom reads can occur from deletes (or inserts). Yes, phantom reads can occur in repeatable read isolation (from inserts only). No, Phantom reads from deletes cannot occur in repeatable read isolation.
under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.

If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blocking every possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactions scope is serializable, and this usually explains the abysmal performance that results.

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

我们仍然先准备好students表的数据：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

Phantom Read

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

在冲突较少的情况下，使用乐观锁。乐观锁因为没有加锁释放锁，也减少了加锁释放锁的开销。
冲突较多时，如果使用乐观锁需要不停地尝试，所以使用悲观锁。
如果乐观锁进行尝试时的花销较大，也是使用悲观锁。

本文部分内容摘自Stack Overflow，以及廖雪峰的sql博客。转载请注明出处。

mysql事务隔离级别 以及 悲观锁-乐观锁

mysql中的 事务隔离级别

mysql事务隔离级别以及悲观锁-乐观锁

mysql中的 `事务隔离级别`