SQL Tutorial

48 / 68

Transactions in MySQL

What is a transaction?

  • A database transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit.
  • All individual operations must succeed or we must revert changes done by each operation in order to call this unit of work as a valid transaction.
  • All transactions have four standard properties - Atomicity, Consistency, Isolation, Durability (ACID).

MySQL transaction features:

  • In MySQL, by default, each statement is a transaction i.e. each modification is instantly committed into the database and you can not rollback the changes made. The behavior of transaction is controlled by autocommit.

  • MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.

  • A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit start transaction or begin statement and ending it with a commit or rollback statement.

  • If autocommit mode is disabled within a session with set autocommit = 0, the session always has a transaction open. A commit or rollback statement ends the current transaction and a new one starts.

  • If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

  • Some statements implicitly end a transaction, as if you had done a commit before executing the statement. For example - create, drop, alter and many others.

  • autocommit -> 1 (ON) (enabled, default value) -> Each SQL statement (until and unless started in an explicit transaction using start transaction) is considered a complete transaction and committed by default when it finishes.

  • autocommit -> 0 (OFF) (disabled, using command set autocommit = 0) -> Subsequent statements acts like a transaction and no activities are committed until an explicit commit statement is issued.

  • If autocommit is 0 and you change it to 1, MySQL performs an automatic commit of any open transaction.

  • To turn off autocommit globally for all so that clients always begin with a default of 0, we have below options: Add a autocommit=0 variable in my.cnf configuration file in MySQL and restart the MySQL server. Start the server with the --autocommit=0 option

  • You can see current autocommit mode using below commands: show variables like 'autocommit'; select @@autocommit;

To illustrate, we need to transfer amount x from account A to account B.

Sample pseudo syntax can be as follows:

-- begin a new transaction
begin work; -- or start transaction; -- or set autocommit = 0;
A.balance = A.balance - x;
If above fails -> rollback;
B.balance = B.balance + x;
If above fails -> rollback;
Update ledger table;
If above fails -> rollback;
Send email and SMS notification to both account holders.
If above fails -> rollback;
commit; --or begin work; to start next new transaction.
-- transaction has ended.

No hints are availble for this assesment

Answer is not availble for this assesment

Loading comments...