当前位置：Gxlcms > 数据库问题 > SQL Server中事务transaction如果没写在try catch中，就算中间语句报错还是会提交

SQL Server中事务transaction如果没写在try catch中，就算中间语句报错还是会提交

时间：2021-07-01 10:21:17 帮助过：13人阅读

TABLE [dbo].[Person]( [ID] [int] IDENTITY(1,1) NOT NULL, [Code] [nvarchar](50) NULL, [Name] [nvarchar](50) NULL, [CreateTime] [datetime] NULL, [UpdateTime] [datetime] NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_CreateTime] DEFAULT (getdate()) FOR [CreateTime] GO 
  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">UNIQUE</span> <span style="color: #0000ff;">NONCLUSTERED</span> <span style="color: #0000ff;">INDEX</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">IX_Person</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Person</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  2. (
  3.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Code</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ASC</span><span style="color: #000000;">
  4. )</span><span style="color: #0000ff;">WITH</span> (PAD_INDEX <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, STATISTICS_NORECOMPUTE <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, SORT_IN_TEMPDB <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, IGNORE_DUP_KEY <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, DROP_EXISTING <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, ONLINE <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, ALLOW_ROW_LOCKS <span style="color: #808080;">=</span> <span style="color: #0000ff;">ON</span>, ALLOW_PAGE_LOCKS <span style="color: #808080;">=</span> <span style="color: #0000ff;">ON</span>) <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">PRIMARY</span><span style="color: #ff0000;">]</span>
  5. <span style="color: #0000ff;">GO</span>

 

Book表：

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Book</span><span style="color: #ff0000;">]</span><span style="color: #000000;">(
  2.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ID</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span> <span style="color: #ff00ff;">IDENTITY</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  3.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookCode</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">nvarchar</span><span style="color: #ff0000;">]</span>(<span style="color: #800000; font-weight: bold;">50</span>) <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  4.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookName</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">nvarchar</span><span style="color: #ff0000;">]</span>(<span style="color: #800000; font-weight: bold;">50</span>) <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  5.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">PersonCode</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">nvarchar</span><span style="color: #ff0000;">]</span>(<span style="color: #800000; font-weight: bold;">50</span>) <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  6.  </span><span style="color: #0000ff;">CONSTRAINT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">PK_Book</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span> <span style="color: #0000ff;">CLUSTERED</span><span style="color: #000000;"> 
  7. (
  8.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ID</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ASC</span><span style="color: #000000;">
  9. )</span><span style="color: #0000ff;">WITH</span> (PAD_INDEX <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, STATISTICS_NORECOMPUTE <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, IGNORE_DUP_KEY <span style="color: #808080;">=</span> <span style="color: #0000ff;">OFF</span>, ALLOW_ROW_LOCKS <span style="color: #808080;">=</span> <span style="color: #0000ff;">ON</span>, ALLOW_PAGE_LOCKS <span style="color: #808080;">=</span> <span style="color: #0000ff;">ON</span>) <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">PRIMARY</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  10. ) </span><span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">PRIMARY</span><span style="color: #ff0000;">]</span>
  11. <span style="color: #0000ff;">GO</span>
  12. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Book</span><span style="color: #ff0000;">]</span>  <span style="color: #0000ff;">WITH</span> <span style="color: #0000ff;">CHECK</span> <span style="color: #0000ff;">ADD</span>  <span style="color: #0000ff;">CONSTRAINT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">FK_Book_Person</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">FOREIGN</span> <span style="color: #0000ff;">KEY</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">PersonCode</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  13. </span><span style="color: #0000ff;">REFERENCES</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Person</span><span style="color: #ff0000;">]</span> (<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Code</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  14. </span><span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">CASCADE</span>
  15. <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">CASCADE</span>
  16. <span style="color: #0000ff;">GO</span>
  17. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Book</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">CHECK</span> <span style="color: #0000ff;">CONSTRAINT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">FK_Book_Person</span><span style="color: #ff0000;">]</span>
  18. <span style="color: #0000ff;">GO</span>

 

可以看到Person表和Book表是一对多关系，一个Person可以有多个Book，所以Book表的PersonCode列是外键，指向Person表的Code列，并为强制约束，也就是说Book表的PersonCode列的值，只能是Person表的Code列值，否则SQL Server会报错：

技术分享图片

 

现在我们执行下面语句给两张表插入数据，我们将插入Person表和Book表的两个Insert语句写在了个事务transaction中，按道理其中一个Insert执行失败，另一个就不会执行：

  1. <span style="color: #0000ff;">BEGIN</span> <span style="color: #0000ff;">TRAN</span>
  2. <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> Person(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Code</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  3. </span><span style="color: #0000ff;">VALUES</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Jack</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  4. </span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Book</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookCode</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookName</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">PersonCode</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  5. </span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">
  6. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B001</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B001</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  7. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B002</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B002</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  8. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  9. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B004</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B004</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  10. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B005</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B005</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">XXX</span><span style="color: #ff0000;">‘</span>)<span style="color: #008080;">--</span><span style="color: #008080;">由于Book表的[PersonCode]列值‘XXX‘在Person表的[Code]列中不存在，所以整个INSERT INTO [dbo].[Book]语句会报错不会执行</span>
  11. <span style="color: #0000ff;">COMMIT</span>

由于值"XXX"在Person表的[Name]列中不存在，所以INSERT INTO [dbo].[Book]语句报错没有执行，但是我们意外地发现INSERT INTO Person却随着事务Commit一起提交了。。。Person表的数据被成功插入了。。。

  1. (1 行受影响)
  2. 消息 547，级别 16，状态 0，第 7 行
  3. <span style="color: #ff0000;">The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Person". The conflict occurred in database "TestDB", table "dbo.Person", column ‘Code‘.</span>
  4. The statement has been terminated.

查询Person表数据：

技术分享图片

查询Book表数据：

技术分享图片

这是因为INSERT INTO [dbo].[Book]语句虽然报错没执行，但是最下面的Commit语句却执行了，也就是说INSERT INTO [dbo].[Book]语句报错，并没有阻止后面Commit语句的执行，所以INSERT INTO Person语句随着事务被提交到数据库生效了。。。

 

现在我们更改上面的语句如下，将两个Insert语句都放到try catch中：

  1. <span style="color: #0000ff;">BEGIN</span> <span style="color: #0000ff;">TRAN</span>
  2. <span style="color: #0000ff;">BEGIN</span><span style="color: #000000;"> TRY
  3.     </span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> Person(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Code</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  4.     </span><span style="color: #0000ff;">VALUES</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Jack</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  5.     </span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Book</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookCode</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">BookName</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">PersonCode</span><span style="color: #ff0000;">]</span><span style="color: #000000;">)
  6.     </span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">
  7.     (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B001</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B001</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  8.     (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B002</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B002</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  9.     (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B003</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  10.     (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B004</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B004</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P003</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  11.     (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B005</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B005</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">XXX</span><span style="color: #ff0000;">‘</span>)<span style="color: #008080;">--</span><span style="color: #008080;">由于Book表的[PersonCode]列值‘XXX‘在Person表的[Code]列中不存在，所以整个INSERT INTO [dbo].[Book]语句会报错不会执行</span>
  12.     <span style="color: #0000ff;">COMMIT</span>
  13. <span style="color: #0000ff;">END</span><span style="color: #000000;"> TRY
  14. </span><span style="color: #0000ff;">BEGIN</span><span style="color: #000000;"> CATCH
  15.     </span><span style="color: #0000ff;">ROLLBACK</span> 
  16. <span style="color: #0000ff;">END</span> CATCH

运行后，我们再查询Person表的数据：

技术分享图片

查询Book表数据：

技术分享图片

我们可以看到这次就和我们的预期一致了，在INSERT INTO [dbo].[Book]语句报错后，后面的Commit语句没有执行，执行了catch中的Rollback，最后两张表的数据都没有插入数据库。

 

SQL Server中事务transaction如果没写在try catch中，就算中间语句报错还是会提交

标签：primary   报错   ati   tran   off   get   server   插入数据   pdb   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！