## Transactions --- ## Tranasactions * ACID Properties * START TRANSACTION * COMMIT * ROLLBACK --- ## ACID Properties * Atomicity * Consistency * Isolation * Durability --- ## Atomicity * a sequence of operations are treated as a single operation * the sequence either fails completely or succeeds completely * if the transaction fails the database is unchanged --- ## Consistency * transactions always preserve database invariants * all constratints that were true before the transaction are still true after the transaction --- ## Isolation * transactions may execute concurrently * the database is always in a state as if the statements had executed sequentially * usually, partial completed transactions are not visible to other transactions --- ## Durability * once committed a transaction stays committed even in the event of a system failure * completed transactions must be recorded in non-volatile memory --- ## Example: Updating a record ```sql UPDATE Employee SET salary = salary + 10000 WHERE name = 'Alice'; ``` --- ## Example: Transferring money from one bank acccount to another id|name |balance --|-----|------- 01|Alice|1000 02|Bob |2500 --- ## Example: Transferring money continued ```sql UPDATE Account SET balance = balance + 500 WHERE name = 'Alice'; -- what if the system fails here UPDATE Account SET balance = balance - 500 WHERE name = 'Bob'; ``` --- ## Transactions in MariaDB * individual DML statements are a transaction * if you want multiple statements to be a transaction, you have to group them in a START TRANSACTION ... COMMIT block * if you discover a problem in the middle of a transaction, you can undo it with a ROLLBACK statement instead of COMMIT --- ## Example ```sql START TRANSACTION UPDATE Account SET balance = balance + 500 WHERE name = 'Alice'; UPDATE Account SET balance = balance - 500 WHERE name = 'Bob'; COMMIT ```