More often than not, update queries need to be atomic in nature. This means that the update query (or a group of update queries) should either run completely or not run at all. SQL provides transactions to help us meet such requirements.
Let us understand this using two examples.
As our first example, consider a user with a bank account; the user makes two separate transactions, one where she deposits 100 dollars and the other where she withdraws 100 dollars. Now, these transactions must be done in a sequence and not at the same time. If the transaction happens at exact same time, then one transaction can overwrite the other one. Thus, if the withdrawal overwrites the deposit, then the user would lose 100 dollars. On the other hand, if deposit overwrites the withdrawal, then the user would gain 100 dollars!
Clearly, we cannot allow these two updates to happen at the same time. If we were to do this atomic, then we would start the first transaction, deposit 100 dollars, and complete the first transaction. After that, we would start the second transaction, withdraw 100 dollars, and complete the second transaction.
As a second example, let us consider a hypothetical case of moving an employee from one project to another. Let us say, the process dictates that we can move an employee to a new project only if there is availability in the new project. For such a case, the movement requires two updates: (a) delete the employee from the earlier project and (b) add the employee to the new project. Clearly, we should execute the first query only if the second query is successful. Otherwise, none of the two queries should be executed.
With SQL's support for transactions, both of these queries can be handled easily. However, before we dive deeper into transactions and locking, let us first understand the MySQL table types that support transactions, since not all MySQL table type support transactions.
MySQL supports a variety of tables and out of those, there are only two types of tables that support transactions: InnoDB and BB. We can use "SHOW ENGINES;" to see the different table types provided by MySQL.
As an example, on a Linux distribution, MySQL supports the following table types. When we look at the Transactions column, we see that only InnoDB type of tables support transaction. For the sake of readability, in the following output, we have formatted the last two columns so that they appear after the the first four columns, but in the next lines.
mysql> SHOW ENGINES; +------------+---------+------------------------------------------------------------+--------------+ | Engine | Support | Comment | Transactions | +------------+---------+------------------------------------------------------------+--------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | CSV | YES | CSV storage engine | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | +------------+---------+------------------------------------------------------------+--------------+ .... .... ------+------------+ XA | Savepoints | ------+------------+ NO | NO | NO | NO | NO | NO | YES | YES | NO | NO | ------+------------+ 5 rows in set (0.00 sec)
Hence, to run transactions on our tables, we would need to ensure that the storage engine for the table is InnoDB. To permanently set the storage engines for all new tables, we can use the set command: "SET storage_engine = InnoDB;". With this config, all new tables will have the storage engine as InnoDB.
For our case, let us focus on the stores table from the blue_sky_toys databases. To understand the engine used by this table, we can use the "SHOW TABLE STATUS LIKE 'stores'" query. To see the storage engines (and other details) for all tables, we can use "SHOW TABLE STATUS" query. Once again, for the sake of readability, we have formatted the output by moving columns into multiple rows.
mysql> SHOW TABLE STATUS LIKE 'stores'; +--------+--------+---------+------------+------+----------------+-------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | +--------+--------+---------+------------+------+----------------+-------------+ | stores | MyISAM | 10 | Fixed | 4 | 131 | 524 | +--------+--------+---------+------------+------+----------------+-------------+ .... .... -------------------+--------------+-----------+----------------+---------------------+ Max_data_length | Index_length | Data_free | Auto_increment | Create_time | -------------------+--------------+-----------+----------------+---------------------+ 36873221949095935 | 2048 | 0 | NULL | 2011-08-13 10:03:51 | -------------------+--------------+-----------+----------------+---------------------+ .... .... ---------------------+------------+-------------------+----------+----------------+---------+ Update_time | Check_time | Collation | Checksum | Create_options | Comment | ---------------------+------------+-------------------+----------+----------------+---------+ 2011-08-13 10:03:51 | NULL | latin1_swedish_ci | NULL | | | ---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec)
The output reveals that the engine type for stores table is MyISAM and therefore, this table would not support transactions. To enable transactions, we would need to alter the engine type to InnoDB using an ALTER query.
mysql> ALTER TABLE stores ENGINE = InnoDB; Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0
If we were to do a "SHOW TABLE STATUS LIKE 'stores'" now, then the output would show that the engine has changed to InnoDB instead of MyISAM. With this change, we are all set to do some basic transactions!
Even with InnoDB, transactions are not triggered by default. Here, we describe two ways in which we can trigger transactions. First is to disable auto commit, which is by default true. This can be done using "SET AUTOCOMMIT = 0;". The second is to explicitly start a transaction using "START TRANSACTION;".
With both of these methods, we can issue multiple queries to the database and all of them would be queued until we run the "COMMIT" query. If we decide to not go with the changes, then we can use "ROLLBACK" and none of the changes would be visible.
mysql> SET AUTOCOMMIT = 0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | | 4 | 440 Sea Way, Springfield | +----------+---------------------------------+ 4 rows in set (0.00 sec) mysql> DELETE FROM stores WHERE store_id = 4; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | +----------+---------------------------------+ 3 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.06 sec) mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | | 4 | 440 Sea Way, Springfield | +----------+---------------------------------+ 4 rows in set (0.00 sec) mysql> SET AUTOCOMMIT = 1; Query OK, 0 rows affected (0.00 sec)
There are two facts worth mentioning in the above output. First, when we issue the query to delete a row from the store, the effect is immediately visible to the current user (when we do a select, then we see one less row), but if were to login from another terminal, then after issuing the delete query, we would continue to see 4 rows. When we do a "ROLLBACK", then we once again, see 4 queries. For a user from the second terminal, it would appear as if nothing happened! If we wanted to really delete the record, all we had to do was issue "COMMIT". Second, we have set AUTOCOMMIT option to be 0 at the beginning and reset it to 1 after the transaction is done.
Depending upon the design, we would not have to do this since AUTOCOMMIT option should ideally be enabled/disabled once and not for every transaction. In fact, as per our earlier design, if we had a set of InnoDB based tables and we wanted all queries to be, by default, transaction-based, then we can disable AUTOCOMMIT. The remaining tables, being MyISAM would not be affected by AUTOCOMMIT since they do not support transactions.
On the other hand, if we wanted to do transactions only for a few queries, then we can use "START TRANSACTION;" to do that:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | | 4 | 440 Sea Way, Springfield | +----------+---------------------------------+ 4 rows in set (0.00 sec) mysql> DELETE FROM stores WHERE store_id = 4; Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | +----------+---------------------------------+ 3 rows in set (0.00 sec)