By: Tibor Nagy
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.
