- \n
- A Transaction in SQL is a single logical unit of work.<\/li>\n
- Transactions can make multiple changes to the database like create tables, delete records, update records, etc in the same database transaction.<\/li>\n
- When all the operations inside a transaction complete successfully, MySQL commits the changes to memory and marks the transaction as complete.<\/li>\n
- A transaction fails if any of the operations in it fail and cause a rollback. A rollback discards changes, they are not permanent.<\/li>\n
- Commit or Rollback is either implicit or explicit.<\/li>\n
- In MySQL implicitly commits transactions with DDL instructions like CREATE, ALTER, RENAME, DROP and TRUNCATE. The changes once made are permanent and users cannot control this.<\/li>\n
- Users can switch off auto-commit for DML instructions like INSERT, UPDATE, DELETE and SELECT.<\/li>\n
- Transactions follow the ACID properties.<\/li>\n<\/ul>\n
3. What are ACID properties?<\/h2>\n
All database systems follow the ACID properties. ACID is the acronym for Atomicity, Consistency, Isolation, and Durability. These properties together ensure that the operations on the data in a database are processed correctly and the database has consistent and reliable data.<\/p>\n
All transactions in a database are atomic in nature.<\/p>\n
3.1 Atomicity<\/h3>\n
Atomicity is equivalent to the \u201cAll or nothing\u201d rule. MySQL transactions are atomic transactions i.e. the transaction processes completely or MYSQL discards the entire transaction. There is no midway i.e. transactions do not occur partially. It involves two operations.<\/p>\n
- \n
- Abort<\/strong>: If a transaction aborts, changes made to the database are discarded and hence not visible i.e. ROLLBACK.<\/li>\n
- Commit<\/strong>: If a transaction commits, changes made are permanent and visible i.e. COMMIT.<\/li>\n<\/ul>\n
4. Syntax<\/h2>\n
As per the My SQL documentation<\/a> the syntax of a transaction is as follows:<\/p>\n
START TRANSACTION\n [transaction_characteristic [, transaction_characteristic] ...]\n\ntransaction_characteristic: {\n WITH CONSISTENT SNAPSHOT\n | READ WRITE\n | READ ONLY\n}\n\nBEGIN [WORK]\nCOMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]\nROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]\nSET autocommit = {0 | 1} <\/pre>\nThe keywords start transaction<\/em> disable autocommit. Otherwise, MYSQL autocommits by default.<\/p>\n
5. Setup for example<\/h2>\n
Forgoing through the examples related to the Commit and rollback transactions, we will consider a new database called \u201cbanking_database\u201d. The banking_database has the following tables: customers, customer_type, accounts, account_types, transactions, and transaction_types. <\/p>\n
For running the queries, we will use multiple sessions of the MySQL Command line client whose documentation is available here<\/a>.<\/p>\n
6. Examples with Commit and Rollback<\/h2>\n
Here, we will see examples of how transaction management works in MySQL. As mentioned earlier, commit and rollback can be implicit or explicit.<\/p>\n
6.1 Implicit Commit<\/h3>\n
In the case of Data Definition Language commands i.e Create, Alter, Rename, etc, MySQL performs an implicit commit. This means that even though a user starts a transaction and sets auto_commit to OFF, My SQL auto commits the DDL statements in the transaction. Hence, the user cannot control this. For example
<\/div> <\/div><\/p>\nSTART TRANSACTION;\nSET AUTOCOMMIT = OFF;\n\n CREATE TABLE CUSTOMERS(\n CUSTOMER_ID INT AUTO_INCREMENT PRIMARY KEY,\n FIRST_NAME VARCHAR(20) NOT NULL,\n LAST_NAME VARCHAR(20),\n CUSTOMER_EMAIL VARCHAR(20),\n CUSTOMER_PHONE INTEGER,\n ENROLLMENT_DATE DATE DEFAULT (CURRENT_DATE())\n );\n \n rollback;\n<\/pre>\n
\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/create_implicit_commit.jpg\")
<\/a>Implicit Commit<\/figcaption><\/figure>\n<\/div>\n Despite the Rollback, MySQL will create the table.<\/p>\n
6.2 Implicit Rollback<\/h3>\n
If there is an error during commands, MySQL rolls back the transaction. We cannot control this unless we handle it using Exceptions. An example of implicit rollback is as follows<\/p>\n
START TRANSACTION;\nSET AUTOCOMMIT = OFF;\n\nCREATE TABLE ACCOUNTS(\n ACCOUNT_ID INT AUTO_INCREMENT PRIMARY KEY,\n ACCOUNT_TYPE INT,\n CUSTOMER_ID INT,\n ACCOUNT_NUMBER VARCHAR(50),\n ACCOUNT_NAME VARCHAR(50),\n FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID) ON DELETE CASCADE,\n FOREIGN KEY (ACCOUNT_TYPE) REFERENCES ACCOUNT_TYPES(ACCOUNT_TYPE_ID) ON DELETE CASCADE\n);\n\nCOMMIT;\n<\/pre>\n
\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/implicit_rollback.jpg\")
<\/a>Implicit Rollback<\/figcaption><\/figure>\n<\/div>\n 6.3 Explicit Commit, DML commands<\/h3>\n
DML i.e Data Manipulation statements like Update, delete , Insert and select need to explicitly committed or rolled back. MySQL by default auto commits and hence if we do not start a transaction, then MYSQL also auto commits all DML statements. However, when we specify the keywords start transaction we need to specify whether we want to commit or rollback the transaction. Given below is an example of Update statement. The same concept applies for Insert, delete, and select statements <\/p>\n
Start transaction;\nset autocommit=off;\n\ninsert into customers\n values\n(default,'John','Doe','john.doe@abc.com',3112221816,default),\n(default,'John','Smith','john.smith@axz.com',3111972097,default);\n\ncommit;\n<\/pre>\n
\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/explicit_commit_update.jpg\")
<\/a>Explicit Commit<\/figcaption><\/figure>\n<\/div>\n 6.4 Mixture of Implicit and explicit commits<\/h3>\n
When we have DML and DDL statements together in a transaction, MYSQL implicitly commits the DDL statements and the DML statements follow the commit or rollback mentioned at the end of the transaction.<\/p>\n
start transaction;\nset autocommit = off;\n\nalter table customers modify CUSTOMER_EMAIL VARCHAR(70);\n\ninsert into customers\n values (default,'Thorin','Oakenshield','thorin.oakenshield@abc.com',NULL,default);\n \nrollback;\n<\/pre>\n
\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/implicit_commit_explicit_rollback-1.jpg\")
<\/a>Mixture of DDL and DML statements in a transaction<\/figcaption><\/figure>\n<\/div>\n 6.5 Nested transactions<\/h3>\n
MYSQL does not allow Nested transactions. If we start a transaction within another, MySQL auto commits all the statements executed in the first transaction till that point irrespective of whether they are DDL or DML statements.<\/p>\n
6.5.1 Nested transactions with 2 DML statements<\/h4>\n
This is an example of 2 DML statements with explicit rollbacks to end both transactions.<\/p>\n
START TRANSACTION;\nSET AUTOCOMMIT = OFF;\n\nINSERT INTO CUSTOMERS\n VALUES\n(DEFAULT,'THORIN','OAKENSHIELD','THORIN.OAKENSHIELD@ABC.COM',NULL,DEFAULT),\n(DEFAULT,'BILBO','BAGGINS','BILBO.BAGGINS@AXZ.COM',1111111111,DEFAULT),\n(DEFAULT,'ARWEN','NOLDOR','BILBO.BAGGINS@SDF.COM',1111111111,DEFAULT);\n \nSTART TRANSACTION; \nUPDATE CUSTOMERS \nSET CUSTOMER_EMAIL = 'ARWEN.NOLDOR@GMAIL.COM',\n CUSTOMER_PHONE = 1239087653\nWHERE FIRST_NAME = 'ARWEN';\n\nROLLBACK;\nROLLBACK;\n<\/pre>\n
\n![\"Nested](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/nested_transaction_implicit_commit.jpg\")
<\/a>Nested transactions<\/figcaption><\/figure>\n<\/div>\n 6.5.2 Nested transactions with error<\/h4>\n
This is an example of nested transactions with the inner transaction failing.<\/p>\n
start transaction;\nset autocommit = OFF;\nupdate customers \nset customer_email = 'arwen.noldor@gmail.com',\n customer_phone = 1239087653\nwhere first_name = 'Arwen';\nstart transaction;\nset autocommit = OFF; \ndelete * from customers;\ncommit;\nrollback;\n<\/pre>\n
\n![\"Nested](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/nested_implicit_commit_implicit-rollback.jpg\")
<\/a>Nested transactions with error<\/figcaption><\/figure>\n<\/div>\n 6.6 Savepoint<\/h3>\n
Along with the commit and rollback statements, MySQL also supports Savepoints. The complete documentation is available here<\/a>. Savepoints are especially useful in large scripts where we can place savepoints at certain points. We can rollback to savepoints in case of errors and not have to roll back the entire transaction. Rollback to a savepoint can be done only during a transaction and we can only roll back to the most recent save point.<\/p>\n
insert into transaction_types \nvalues\n(1,'Debit'),\n(2,'Credit'),\n(3,'Remittance');\n\ninsert into account_types\nvalues\n(1,'Savings'),\n(2,'Current'),\n(3,'Loan'),\n(4,'Flexi Deposit account');\n\nsavepoint first_savepoint;\n\ninsert into accounts\nvalues\n(1,2,3,'C0000111134789','ThorinCurrentAccount');\n\ndelete from customers where customer_id = 1;\n\nrollback to first_savepoint;\n\ninsert into customer_type\nvalues (default,5,'Savings Customer',NULL);\n\ncommit;\n<\/pre>\n
\n![\"Savepoint\"](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/savepoint_rollback.jpg\")
<\/a>Savepoint<\/figcaption><\/figure>\n<\/div>\n 6.7 Release Savepoint<\/h3>\n
Savepoint can be released after the transaction that needs them is completed. We can release savepoint only in the same session and not across the session. To release a savepoint<\/p>\n
release savepoint first_savepoint;<\/pre>\n
- Commit<\/strong>: If a transaction commits, changes made are permanent and visible i.e. COMMIT.<\/li>\n<\/ul>\n
- Abort<\/strong>: If a transaction aborts, changes made to the database are discarded and hence not visible i.e. ROLLBACK.<\/li>\n
![\"release](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/01\/savepoint_usage.jpg\")
<\/a>