0

I knew that update, insert, delete start a transaction in auto commit mode, I would like to ask if select statements make their own transaction also in auto commit mode or there is no need for that ? and also I would like to know if DDL are auto committed and start their own transaction

Ps: I'm using SQL server if that may make the answer different from one DBMS to another.

2 Answers 2

3

This is something that you can verify by using the transaction DMVs. For example, if I kick off a long running SELECT query then the following query will return at least one row:

SELECT *
FROM sys.dm_tran_active_transactions t
WHERE name = N'SELECT';

Example result set:

enter image description here

You can use the same method for DDL statements, but it's worth considering what should happen if an ALTER TABLE fails before completion. You'd want the table to be restored to the original state, right? Otherwise your database will be corrupt. A transaction is the mechanism that allows for the statement to be rolled back. You will have a transaction with log records any time a statement modifies data in your database in order to avoid database corruption. So yes, DDL statements must use transactions.

2
  • that really helped me knowing that select and DDL start a transaction in auto commit, but what's the purpose of having a select statement in a transaction since it will just retrieve data so if any error happens the data won't be affected and we don't need a log file to know that a failure happened in reading Nov 26, 2021 at 22:10
  • The transaction holds the Sch-S and possibly S locks that ensure that the query sees a correct and consistent view of the table's structure and data. Nov 27, 2021 at 18:45
3

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

Even selects or DDL statements have their own implicit transaction and so they have autocommit to. But usually a select has nothing to commit.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.