Do select statements start a transaction in auto commit mode?

Question

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.

Answer 1

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:

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.

Answer 2

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.