Transactions
One of the common shortcomings of the novice sql guru is the lack of knowledge about transactions.
What is a transaction?
A transaction consits off several statements which are executed as a single unit. Either all the statements are done or they all fail. You’ll need this functionality in situations which require atomicity. For instance you are coding for a bank and your code has to transfer money from one account to another.
Sample without transactions:
UPDATE accounts SET money = money - 200 WHERE id = 1
–> the server crashes here
UPDATE accounts SET money = money + 200 WHERE id = 2
–> the server crashes here
UPDATE accounts SET money = money + 200 WHERE id = 2
Now the first guy has lost his money and the second guy never received the money.
Sample with transactions:
BEGIN TRANSACTION
UPDATE accounts SET money = money - 200 WHERE id = 1
–> the server crashes here
UPDATE accounts SET money = money + 200 WHERE id = 2
COMMIT
UPDATE accounts SET money = money - 200 WHERE id = 1
–> the server crashes here
UPDATE accounts SET money = money + 200 WHERE id = 2
COMMIT
Now when the server is recovered the first sql statement has never happend so the first guy still has all his money.