Yes, MySQL DBAs can learn PostgreSQL! This series is for those who know MySQL and want to expand their knowledge, see how another database works, or are looking to expand their career horizons. In this episode we will look at transactions. Yes, MySQL with InnoDB does have the ability to perform transactions and this is a case where both MySQL and PostgreSQL work pretty much the same way. But you do get named rollback points and a handy reminder when using PSQL that you are in a transaction when you use PostgreSQL. The supporting video with bonus material can be found here.
So why emphasize transactions? There are a lot of developers who do not use them on a regular basis, if at all. It helps if a DBA not only has a solid knowledge of the subject but good examples to pass on to others.
So what is a transaction?
A transaction allows the grouping of one or more queries as a set. And that set can be committed or written to the database as a complete set. Or that set can be backed out without changing the database. The classical example is moving money from one bank account to another. If something goes wrong the entire transaction is canceled. If all goes well, the money is deducted from the first account and added to the second,
And to make this even more complex, you can have save points within a transaction so you can back up to that point but not lose all the work to that point.
1 2 3 4 5 6 7 8 9 10 11 12 13 | test=# START TRANSACTION test=*# insert into z (z) values (1); INSERT 0 1 test=*# savepoint a; SAVEPOINT test=*# insert into z (z) values (2); INSERT 0 1 test=*# select * from z; z --- 1 2 (2 rows) |
In the above example we start our transaction by issuing the START TRANSACTION statement. From this point of time until the data is committed, the data is treated as one group of commands to be executed together. Consider metaphorically this as the data is being written on a dry-erase board in the server to make sure it is correct before you commit to carving it in stone when happy with the product. The first statement after the transaction is started in an insert of some data. We insert a row where the value of z is 1.
You may note that psql changes the prompt from test=# to test=*# to remind you that you are in a transaction. You are not going to get that reminder with the standard MySQL Clients.
Next we create a save point, here creatively named ‘A.’ Note here that we can name the save points so the transaction can pinpoint what to back out of the transaction. This is another metaphorical dry-erase board for what comes after. For some reason we are happy with the row with z = 1 as a good thing but the next statement may be something we are uncertain about. We start a save point that we can return to, erase the newest stuff on our imaginary dry-erase board, and proceed to add a second row of data. This time we add a row where z = 2.
So maybe we are happy with these two rows, or maybe not. We are undecided but carry on anyway.
1 2 3 4 5 6 7 8 9 10 11 | test=*# savepoint b; SAVEPOINT test=*# insert into z (z) values (3); INSERT 0 1 test=*# select * from z; z --- 1 2 3 (3 rows) |
At this point, we logically want another one of those imaginary dry-erase boards and issue a command to set a save point named ‘B.’ We add data and take a peek at it. But when we look at the data we have entered, something does not make us happy and we want to remove the last two rows from the dry-erase board.
1 2 3 4 5 6 7 | test=*# rollback to savepoint a; ROLLBACK test=*# select * from z; z --- 1 (1 row) |
Something tells us that we do not need the last two rows. We issue a ROLLBACK TO SAVEPOINT, and all commands issued since we set that rollback are wiped off the dry-erase board. So within our transaction we have only the first row.
1 2 3 4 5 6 7 8 | test=*# rollback; ROLLBACK test=# select * from z; z --- (0 rows) test=# |
Please note that just using ROLLBACK without a named save point wiped clean the dry-erase board, er, activities in the transaction to the point where we typed START TRANSACTION. Usually you want to go back to a named save point and not wipe out everything, similar in action to forgetting a WHERE clause on a DELETE statement. So know where you really want to go back to.
But how does one do a real transaction?
1 2 3 4 5 6 7 8 9 10 11 | test=# start transaction; START TRANSACTION test=*# insert into z (z) values (101); INSERT 0 1 test=*# commit; COMMIT test=# select * from z; z ----- 101 (1 row) |
When the COMMIT statement is processed, everything between the START TRANSACTION and the COMMIT is executed as a block. This blog is not the place to go into ACID compliance or isolation levels, but for now take it for granted that all go in together. Metaphorically we like what we see on the dry-erase board and convert it to carved stone in the database.
1 2 3 4 5 6 7 8 9 10 11 | test=# BEGIN; BEGIN test=*# update z set z = 102; UPDATE 1 test=*# commit; COMMIT test=# select * from z; z ----- 102 (1 row) |
And you can use BEGIN to start a transaction if you so desire.
Summary
So transactions are transactions in both MySQL and PostgreSQL. But you get more flexibility for rollbacks with PostgreSQL. The past videos for PostgreSQL for MySQL Database Administrators (DBA) can be found here: episode one, episode two, episode three, episode four, episode five, episode six, episode seven, episode eight, episode nine, episode ten, and episode eleven.
Let me clear something up, please. MySQL does have named rollbacks.