一、数据库使用条件
1.mysql常用的引擎分为innodb和myisam引擎,只有innodb引擎支持事务;oracle没有引擎的概念,支持事务.
2.mysql5.5.5以上默认Engine是Innodb,默认事务隔离级别是repeatable
二、概念
官方原文
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back
事务就是一个能被回滚或者提交的工作的原子单位。当事务成功时,事务里面的操作会持久化到数据库;当事务失败回滚,事务里面的操作不会对数据库执行,数据库实际是没有变化的.
三、四大特性
1.原子性
官方原文
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
事务是就是一个能被回滚或者提交的工作的原子单位
2.一致性
官方原文
The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.
不管事务处于哪一个阶段,数据库任何数据状态都是一致,数据不会同时出现,既能看到修改前的数据,又能看到修改后的数据,要么看到修改前数据,要么要看到修改后的数据
3.隔离性
Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved the through locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other
事务都是独立执行,要么提交成功,要么提交失败
4.持久性
The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB
, the doublewrite buffer assists with durability.)
事务一旦执行成功,对应的改变会持久化数据库的数据。其实数据库的数据都在存储的硬盘,就是事务成功,根据事务的操作,对硬盘的上数据做修改
四、隔离机制
不同隔离机制会造成的问题
1.dirty read(脏读)
An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.
一个事务能看到别的事务未提交的修改。
2.phantom read(幻读)
A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE
clause of the query.
在一个事务做了几次查询,查询期间,有事务新增或修改或删除数据,导致每次查询,where条件一样,但查询的结果变多或者变少
3.non-repeatable read(不可重复读)
The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime)
在一个事务做了几次查询,几次查询期间,有事务对数据修改,导致每次查询相同的数据,这个数据每次的查询结果都不一样
隔离级别
1.read committed
An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.
When a transaction with this isolation level performs UPDATE ... WHERE
or DELETE ... WHERE
operations, other transactions might have to wait. The transaction can perform SELECT ... FOR UPDATE
, and LOCK IN SHARE MODE
operations without making other transactions wait.
a事务第一次对数据做查询,b事务内对数据做修改,b事务提交了,a事务再次查询,能看到修改后的数据;如果b事务没有提交,a事务再次查询,结果和上一次查询一样..这个事务级别避免脏读,但还会造成幻读、不可重复读
The isolation level that provides the least amount of protection between transactions. Queries employ a locking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known as dirty read). Use this isolation level with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only do queries, not insert, update, or delete operations
a事务第一次对数据做查询,b事务内对数据做修改,不管b事务有没有提交,a事务再次查询,能看到修改后的数据.这个事务级别造成脏读、幻读、不可重复读.这个事务级别最好只做查询
The default isolation level for InnoDB
. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.
When a transaction with this isolation level performs UPDATE ... WHERE
, DELETE ... WHERE
, SELECT ... FOR UPDATE
, and LOCK IN SHARE MODE
operations, other transactions might have to wait.
a事务内不管对相同的数据做多少次查询,每次查询出来的数据结果都是一样的,因为数据来自一样的快照.这个事务级别避免脏读,不可重复读,还是会有幻读问题
注:数据库对数据每一次修改,数据都会有一个快照版本,相当数据的修改历史
4.serializable
The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait
This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level for InnoDB
is the next most strict, REPEATABLE READ
最保守的事务级别,任何事务的提交都会造成当前事务等待.这个事务级别避免脏读,不可重复读幻读
注:上述4个隔离级别只有mysql才有,oracle只有read uncommitted和serializable