MySQL to SQL Server Transaction Differences


By:
Overview

The most important characteristics of transaction handling are similar in Microsoft SQL Server's Transact SQL and MySQL, for example, they both use commit and rollback statements and the default mode is autocommit. However, there are certain differences.

Explanation

The following code snippets show examples of the transaction management functions that are different in the two database management system. The first code is for MySQL and the second code is for MS SQL.

Start a transaction:

START TRANSACTION [WITH CONSISTENT SNAPSHOT]
-- BEGIN can be also used

BEGIN TRANSACTION [transaction_name] [WITH MARK 'description for log']
-- you can also use BEGIN TRAN

Commit a transaction:

COMMIT [WORK] [AND [NO] CHAIN | [NO] RELEASE]

COMMIT TRANSACTION [transaction_name]
-- you can also use COMMIT TRAN

Rollback a transaction:

ROLLBACK [WORK] [AND [NO] CHAIN | [NO] RELEASE]

ROLLBACK TRANSACTION [transaction_name]
-- you can also use ROLLBACK TRAN

Add a savepoint:

SAVEPOINT identifier

SAVE TRANSACTION savepoint_name
-- you can also use SAVE TRAN

and rollback to savepoint:

ROLLBACK [WORK] TO [SAVEPOINT] identifier

ROLLBACK TRANSACTION savepoint_name
-- you can also use ROLLBACK TRAN

Modify autocommit property to require an explicit commit or rollback statement:

SET autocommit = {0 | 1}

SET IMPLICIT_TRANSACTIONS { ON | OFF }
-- note that the default is off which means that autocommit is active


Additionally MS SQL supports the usage of @@TRANCOUNT which returns the number of active transactions for the actual connection.


Last Update: 5/27/2011




Comments For This Article

















get free sql tips
agree to terms