SQL Server transaction mode

According to Transactions (Transact-SQL) - SQL Server | Microsoft Learn Sql server provide below transaction mode

1. Autocommit transactions
   Each individual statement is a transaction. sql server 默认设置
   每一句话manually select 之后， 点击execute , 选中的script 会被当成是一个transaction

2. Implicit transactions
   Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
   开启这个模式之后，需要手动添加在需要commit 和 rollback的地方，增加了灵活性 ，比如如下代码

begin transaction
if XXX;
UPDATE XXXX ;
COMMIT;
IF XXX:
UPDATE XXX
ROLLBACK

哪里配置？ To navigate to Options Select Tools>Options>Query Execution>SQL Server>ANSI in your Microsoft SQL Server Management Studio.

! [image.png] (https:upload-images.jianshu.io/upload_images/1701616-682c6217a415066d.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

打勾进入 implicit mode 这个是永久设置
也可以用命令 set implicit_transations on/off 但是这个不是永久设置，只在当前的session 有效

3. Explicit transactions
   A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

4. Batch-scoped transactions
   写GO 关键字可以自行组织 batch transaction

参考这个文章，比较详细
Auto Commit, Implicit & Explicit SQL Transactions Explained (coding-examples.com)