Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In the article, I will explain What is Transaction in MySQL. Also, article contains information about the use of Transaction, which is frequently used in systems such as SQL Server, Oracle etc..
What is Transaction in MySQL?
Transaction is a feature that allows database management systems to perform transactions according to ACID rules Transaction provides integrity, consistency, independence and durability in ACID rules. Thus, data is restored when errors occur during any change.
Transaction usage in MYSQL
The INNODB storage engine is used to use the Transaction feature with MySQL. Let’s create the products table where the products are stored.
1 2 3 4 5 | CREATE TABLE products ( product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255) NOT NULL, product_price DECIMAL(10,0) NOT NULL ) ENGINE=INNODB; |
BEGIN TRANSACTION in MySQL
One of the following is applied to start the transaction.
1 | START TRANSACTION; |
or
1 | BEGIN; |
SQL commands executed after the Transaction feature is started cannot perform any inserts, deletes or updates on the table.
The transactions performed are temporarily stored in memory.
COMMIT and ROLLBACK Transaction in MySQL
To reflect the transactions performed within the transaction to the table, below command is used.
1 | COMMIT; |
To rollback the transactions performed within the transaction, below command is used.
1 | ROLLBACK; |
The Transaction feature is mostly used within MySQL stored procedures with the MySQL if statement for error checking. With the MySQL if statement, errors are checked, and as a result, transactions are committed to the table or rolled back.
I wish you a good day.
