Only Few Seats Left for Advanced Certification Courses on Data Science, ML & AI by E&ICT Academy IIT RoorkeeApply Now
What is a transaction?
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
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
autocommit mode is disabled within a session with
set autocommit = 0, the session always has a transaction open. A
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.
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:
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;
Example - We need to transfer amount x from account A to account B.
-- 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