There are a few keywords in MySQL to ensure your SQL queries are in a transaction. To start a transaction in MySQL, use the START TRANSACTION or BEGIN keywords. To commit the transaction, you can use the COMMIT keyword. To rollback a transaction, use the ROLLBACK keyword. 

To see a transaction in action, you can execute a query that we used earlier on in this chapter, but this time in a transaction: 

USE lahmansbaseballdb;
START TRANSACTION;
UPDATE managerscopy 
SET lgID = '--'; 

The previous query updates the managerscopy table and sets all the lgID values to '--', but this time, it's within a transaction, which means if you didn't like the results of the update, you can roll it back. If you are pleased with the results, you can issue a COMMIT command, but if you aren't pleased with them, then you can issue a ROLLBACK command instead. If you don't commit your transaction right away, users in other sessions querying the managerscopy table won't see the changes you've made until you issue the COMMIT command.

You can also issue the COMMIT command along with the original query, as in the following query: 

USE lahmansbaseballdb;
START TRANSACTION;
UPDATE managerscopy 
SET lgID = '--';
COMMIT;

The previous query commits the transaction right after it's done so that all users can see the changes you've made right away. When you test your query, it's a good idea to not issue the COMMIT command right away and check the results of your transaction first before issuing the COMMIT command. This way, you can issue a ROLLBACK command instead, if the results of the UPDATE, DELETE, or INSERT commands weren't as you expected. 

The following query uses BEGIN instead of START TRANSACTION, but it does the same thing as the previous query: 

USE lahmansbaseballdb;
BEGIN;
UPDATE managerscopy 
SET lgID = '--';
COMMIT;

A good use case for using a transaction in the lahmansbaseballdb table may be if you want to copy records from one table to another, as in the following query: 

USE lahmansbaseballdb;
CREATE TABLE awards LIKE awardsmanagers; 
START TRANSACTION; 
INSERT INTO awards 
SELECT * FROM awardsmanagers
WHERE awardid = 'BBWAA Manager of the Year';
DELETE FROM awardsmanagers 
WHERE awardid = 'BBWAA Manager of the Year';

You check the results of the previous query and realize that you already had the 'BBWAA Manager of the Year' field in the awards table, so you don't want to copy it there but you don't want to delete it out of the other table since you want to keep it in the awardsmanager table. This is a good case for issuing a ROLLBACK statement, as in the following statement: 

ROLLBACK; 

However, let's say that you were happy with the results of the transaction; so, instead, you would execute a COMMIT statement, as in the following query:

COMMIT; 

If you want to automate the copying of data, as in the previous transaction, you need to put the COMMIT statement in the query so that each time the query runs, it commits automatically, instead of waiting for user intervention to commit, as in the following query: 

USE lahmansbaseballdb;
START TRANSACTION; 
INSERT INTO awards 
SELECT * FROM awardsmanagers
WHERE awardid = 'BBWAA Manager of the Year';
DELETE FROM awardsmanagers 
WHERE awardid = 'BBWAA Manager of the Year';
COMMIT; 

The previous query commits once it's completed successfully, but still rolls back if it encounters any errors. 

Let's have a look at an example where the transaction auto-commits because you used a DDL statement in your transaction: 

USE lahmansbaseballdb;
START TRANSACTION; 
INSERT INTO awards 
SELECT * FROM awardsmanagers
WHERE awardid = 'BBWAA Manager of the Year';
DROP TABLE lahmansbaseballdb.managerscopy;
DELETE FROM awardsmanagers 
WHERE awardid = 'BBWAA Manager of the Year';
COMMIT; 

Everything before the point where you issued DROP TABLE in the transaction is committed, even though you didn't explicitly use the COMMIT statement. Be careful not to place DDL changes in the middle of your transaction because this will cause changes to values that you may not want to auto-commit, and they will commit even if they failed to complete correctly. 

MySQL also allows you to turn off auto-commit for your session. You can turn off auto-commit by executing the following script: 

SET autocommit = OFF; 

Once you execute the previous script, you will need to execute a COMMIT statement if you want any INSERT, UPDATE, or DELETE queries to become permanent changes to the database. 

To turn auto-commit back on, execute the following script: 

SET autocommit = ON; 

Remember that MySQL automatically commits changes to the database by default, unless you use a transaction or turn off auto-commit for your session. 

MySQL also supports some additional transaction-based keywords. You can utilize savepoints within a transaction to rollback to a specific point in the transaction. You do this by naming the SAVEPOINT and rolling back to it. The following query shows you how to do this: 

USE lahmansbaseballdb;
START TRANSACTION;
SAVEPOINT firstsavepoint;
INSERT INTO awards
SELECT * FROM awardsmanagers
WHERE awardid = 'BBWAA Manager of the Year';
SAVEPOINT secondsavepoint;
DELETE FROM awardsmanagers
WHERE awardid = 'BBWAA Manager of the Year';
ROLLBACK TO firstsavepoint;

In the previous query, a SAVEPOINT value called firstsavepoint is created after you start the transaction. Then, another SAVEPOINT value is created after you insert data into the awards table. At the end of the query, a ROLLBACK query is issued, which undoes all the changes back to the first SAVEPOINT instance, which in this case is everything in the transaction. You could instead issue a ROLLBACK statement to secondsavepoint. This would only undo the DELETE statement in the transaction. If you issued a ROLLBACK statement without the SAVEPOINT name, the savepoints would no longer be saved and the entire transaction would be rolled back. If you wanted to commit this transaction instead of rolling back, you would issue the COMMIT statement; the whole transaction would be committed and the savepoints removed. The same limitations apply as in a transaction. If you issue a statement such as a drop table in the middle of your transaction or savepoint, all the transactions before that point will be implicitly committed.