In the Understanding Database Transactions blog, we leaned how transactions are a fantastic way to guard against data loss and inconsistencies by guaranteeing that all operations performed with a transaction succeed or fail together. In today's follow-up, we'll learn how to employ a transaction within a stored procedure in order to ensure that all tables involved remain in a consistent state.
About the sp_delete_from_table Stored Procedure
If you've read any of my previous blog articles, you probably know that I often illustrate new concepts using the Sakila Sample Database. And why not? It was developed specifically as a learning database for MySQL. If you aren't already aware, the Sakila Sample Database contains data pertaining to a fictitious movie rental store chain. Besides tables and views, you'll also find user functions, triggers, queries, and stored procedures that illustrate most commonly used database objects and tasks.
One of the stored procedures that is especially relevant to this blog is sp_delete_from_table. It accepts three input parameters as follows:
- @table: the name of the table from which to delete rows.
- @whereclause: the criteria for identifying which rows to delete.
- @delcnt: how many rows we expect to be deleted.
The procedure returns the @actcnt (bigint) output parameter that contains the number of rows that were actually deleted.
Here's the full definition as shown in Navicat Premium:
Important Transaction Statements
Relational databases provide us with several important statements to control transactions:
- To start a transaction, use the BEGIN TRANSACTION statement. Both START or BEGIN WORK are aliases of the BEGIN TRANSACTION. You'll find it on line 17 of the sp_delete_from_table procedure.
- To commit the current transaction and make its changes permanent, use the COMMIT statement. That happens on line 32 of the procedure.
- To roll back the current transaction and cancel its changes, use the ROLLBACK statement. There are a couple of situations where that comes up in the code:
- If the statement would have deleted all rows in the table, a message is displayed and the transaction is rolled back on line 26.
- Should the number of rows deleted not match the number that you expected, again, a message is displayed and the transaction is rolled back. That happens on line 38.
- To disable or enable the auto-commit mode for the current transaction, use the SET autocommit statement. By default, some databases, such as MySQL, run with autocommit mode enabled by default. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect. However, if an error occurs during statement execution, the statement is rolled back. Since most of the work takes place within a transaction in the sp_delete_from_table procedure, the SET autocommit statement is not needed.
Testing a Transaction Rollback
Since we know that the sp_delete_from_table procedure will abort if the expected count does not match the actual number of rows deleted, we can test for rollbacks by either making sure that our @whereclause criteria would delete every row in the table or by simply providing a @delcnt value that we know won't match. Let's try the latter.
In Navicat, we can run a stored procedure from the editor via the Execute button. Clicking it causes a dialog to come up which accepts input parameters (output params may be ignored):
After the procedure terminates, we can see output messages in the Message tab. We can see that it has been rolled back as expected:
Conclusion
In today's blog, we learned how to employ a transaction within a stored procedure in order to ensure that all tables involved remain in a consistent state, no matter the outcome.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
