How do transactions work in Microsoft’s SQL Server?
Microsoft’s SQL Server provides support for transactions in three different modes of operation. Those three modes are known as explicit, implicit, and autocommit. If you are connected directly to the database using some sort of client side tool then you can use any of those three modes as you desire. But, if you are connecting to SQL Server through either a JDBC or an ODBC driver, then you should read the documentation to see what kind of transaction support is provided.
What is explicit mode in SQL Server?
When explicit mode is used, every transaction starts with a BEGIN TRANSACTION statement and ends with either a ROLLBACK TRANSACTION statement (for when a transaction does not successfully complete) or a COMMIT TRANSACTION statement (for when a transaction completes successfully). Explicit mode is most commonly used in triggers, stored procedures and application programs.
What is implicit mode in SQL Server?
Implicit mode for transactions in SQL Server is set to ON or OFF by using the command SET IMPLICIT_TRANSACTIONS to ON or OFF. If the implicit mode is set to ON, then a new transaction is implicitly started whenever any particular SQL statement is executed. The particular SQL statement can be one from a specific list of SQL statements, that includes INSERT, SELECT, DELETE AND UPDATE – and other SQL statements as well. It is called implicit mode because of the fact that once IMPLICIT_TRANSACTIONS is set to ON then the transactions are created implicitly for certain SQL statements, without having to say you want to create a transaction each time.
If a transaction is implicitly started, then the transaction will continue until it’s either fully committed or until the transaction is rolled back. One scenario in which the transaction could potentially be rolled back is if the user disconnects before having submitted a statement that would end the transaction.
What is autocommit mode in SQL Server?
In autocommit mode, each SQL statement is treated as a separate transaction. This is accomplished by automatically committing each SQL statement as it finishes – and is why it’s called autocommit mode. Autocommit is used by default in every connection to SQL Server unless the implicit mode is set or an explicit transaction is started.