Financial operations typically need transactions, and theguarantees of data integrity outweigh the cost of additional overhead. Transactional properties are essential for some applications and notfor others, and you can choose which ones make the most sense for yourapplications. MySQL offers some storage engines that are transaction-safe (such asInnoDB and BDB), and some that are not transaction-safe (such as MyISAM andMEMORY). Transactional processing provides stronger guarantees about the outcome ofdatabase operations, but also requires more overhead in CPU cycles, memory, anddisk space. Isolation: One transaction has no effect onanother.ĭurability: When a transaction executes successfully tocompletion, its effects are recorded permanently in the database. In other words, the transaction doesn't make amess of your database. You can't have just some of them execute.Ĭonsistency: The database is consistent before and afterthe transaction executes. ACID is an acronym for Atomic, Consistent, Isolated, and Durable,referring to four properties that transactions should have:Ītomicity: The statements a transaction consists of forma logical unit. Transactional systems typically are characterized as providing ACIDproperties. A transaction groups statements into asingle execution unit to prevent concurrency problems that could otherwise occurin a multiple-client environment. In this case, differentclients might interfere with each other. (You may still have to determine which transactionsweren't entered and re-issue them, but at least you don't have toworry about half-transactions making your database inconsistent.)Īnother use for transactions is to make sure that the records involved in anoperation are not modified by other clients while you're working with them.MySQL automatically performs locking for single SQL statements to keep clientsfrom interfering with each other, but this is not always sufficient to guaranteethat a database operation achieves its intended result, because some operationsare performed over the course of several statements. The rollback capabilities of transaction support allow you to handle thissituation properly by undoing the effect of the statements that executed beforethe error occurred.
If transactional capabilities are not available toyou, you have to figure out the state of ongoing operations at crash time byexamining your logs manually in order to determine how to undo them or completethem. If a crash occurs between the two statements, the operation is incomplete.Depending on which statement executes first, Bill is $100 short without Bobhaving been credited, or Bob is given $100 without Bill having been debited.Neither outcome is correct.
The canonical example of this involves afinancial transfer where money from one account is placed into another account.Suppose that Bill writes a check to Bob for $100.00 and Bob cashes the check.Bill's account should be decremented by $100.00 and Bob's accountincremented by the same amount: UPDATE account SET balance = balance - 100 WHERE name = 'Bill' UPDATE account SET balance = balance + 100 WHERE name = 'Bob' Any statements executed up to that point within thetransaction are undone, leaving the database in the state it was in prior to thepoint at which the transaction began.Ĭommit and rollback provide the means for ensuring that halfway-doneoperations don't make their way into your database and leave it in apartially updated (inconsistent) state. If an error occurs during the transaction, you rollit back to cancel it. If all of thestatements in the transaction succeed, you commit it to record their effectpermanently in the database. This isachieved through the use of commit and rollback capabilities. Either allthe statements execute successfully, or none of them have any effect. A transaction is a set of SQL statements that execute as a unit.