mssqltips logo

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools