Exercise 14: Basic Transactions

Imagine if the SQL in your last exercise had an error half-way through its run and it aborted. You may have even run into this problem, and then you see that your database is now seriously broken. You've been getting away with this because you have a big code.sql file that rebuilds your database, but in a real situation you can't trash your whole database when you mess up.

What you need to make your script safer is the BEGIN, COMMIT, and ROLLBACK commands. These start a transaction, which creates a "boundary" around a group of SQL statements so you can abort them if they have an error. You start the transaction with BEGIN, do your SQL, and then when everything's good end the transaction with COMMIT. If you have an error, then you just issue ROLLBACK to abort what you did.

For this exercise I want you to do the following:

• Take your ex13.sql and copy it to ex14.sql so you can modify it.

• Once you have that, put a BEGIN at the top and a ROLLBACK. At the bottom.

• Now run it and you'll see that it's as if your script didn't do anything.

• Next, change the ROLLBACK to be COMMIT and run it again, and you'll see it works like normal.

• Get rid of the BEGIN and COMMIT from your ex14.sql so it's back the way it was.

• Now create an error by removing one of the TABLE keywords from one of the lines. This is so you can make it have an error and recover.

Once you have this broken ex14.sql you'll play with it in the sqlite3 console to do a recovery: