In this video, I take a look at the three most important transaction modes in SQL Server: autocommit, explicit transactions, and implicit transactions. Sorry, batch-scoped transactions, but nobody loves you.
Links:
- MARS and batch-scoped transactions: https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-ver15
- Microsoft Docs on transactions: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-ver15
- ACID properties: https://www.c-sharpcorner.com/UploadFile/84c85b/understanding-transactions-in-sql-server/
- Performance of looping with one transaction versus one per iteration: http://www.theboreddba.com/Categories/miscellaneous/SQL-Server-AUTOCOMMIT-within-a-while-loop.aspx
- Nested transactions are broken: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/
