Transactions

SQLyog provides GUI controls for executing transactional command. The controls available are a menu category and 3 buttons for executing most common transactional commands.

Set Autocommit:

If it is checked then Autocommit is set to 1. If Autocommit is set, any changes/modifications made to a table will be stored permanently by MySQL and the change cannot be reverted.

Isolation levels: 

Repeatable Read: This is the default Isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions.It uses a moderately strict locking strategy.

Read Committed: Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started.

Read Uncommitted: The data that has been changed by other transactions and not committed yet can be read by the current transaction (known as dirty read). This option provides the least amount of protection between transactions.  Use this with Caution.

Serializable: The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. Any attempt to change data that was committed by another transaction since the start of the current transaction will cause the current transaction to wait.

Start Transaction: 

With no modifier: Simply Starts a transaction without any option specified. Executes the query  “ START TRANSACTION “.

With Consistent Snapshot: This option applies only to InnoDB Storage Engine with REPEATABLE READ Isolation level. If something else is set, it will be simply ignored. It simply starts a transaction with consistent read. The two option specified with this : Read only and Read Write Set the transaction access mode permitting or prohibiting changes to the table used in transaction.

Read Only: Allows only Read operations to be performed hence prohibiting any changes to the tables.

Read Write: Allows Both read and write operations hence permitting changes in the tables.

Commit: 

This option will commit/save any changes made to the tables in the current transaction. Various supported options are :

With no modifier:  Simply commits a transaction without specifying anything. Executes a COMMIT query.

Chaining option: 

AND CHAIN: This option causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction.

AND NO CHAIN: This option will explicitly indicate that a new transaction is not started after the current transaction terminates. This option overrides the server default (server system variable) chaining option and force the NO CHAIN.

Release option:

RELEASE: This option causes the server to disconnect the current client session after terminating the current transaction. SQLyog will then reconnect automatically as it always will when a connection is lost.

NO RELEASE: This option will just terminate the current transaction. This option overrides the server default (server system variable) release option and force it to NO RELEASE.

Savepoint: 

Create Savepoint: Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

Release Savepoint: Removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. If a non-existing save point is released, then it will result in an error.

Note: All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.

Rollback: 

Transaction: This will rollback the entire transaction and any changes made in the transaction will be reverted.

To Savepoint: This will rollback the transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. Any Savepoints that were set after the named Savepoint will be lost.

Chaining option: 

AND CHAIN: This option causes a new transaction to begin ending the current transaction, and the new transaction has the same isolation level as the just-terminated transaction.

AND NO CHAIN: This option will end the transaction and rollback the changes irrespective of the server variable set for chaining option.

Release option:

RELEASE: This option causes the server to disconnect the current client session after rolling back the changes made in the current transaction. SQLyog will then reconnect automatically as it always will when a connection is lost.

NO RELEASE: This option will just Rollback the current transaction. This option overrides the server default (server system variable) release option and force it to NO RELEASE.

Note: DDL statements like create, drop, alter cannot be rolled back. Executing these statements commits the transaction. SQLyog displays a warning while executing these statements.