# DB transactions on SQL Server

**URL:** <https://forum.servoy.com/t/db-transactions-on-sql-server/19321>\
**Category:** Classic Servoy\
**Created:** [June 26, 2017, 1:12pm UTC](https://forum.servoy.com/t/db-transactions-on-sql-server/19321 "2017-06-26T13:12:11Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![pauwel.demeyer](https://avatars.discourse-cdn.com/v4/letter/p/3be4f8/32.png) [@pauwel.demeyer](https://forum.servoy.com/u/pauwel.demeyer)\
**Post date:** [June 26, 2017, 1:12pm UTC](https://forum.servoy.com/t/db-transactions-on-sql-server/19321/1 "2017-06-26T13:12:11Z")

</div>

While looking at the generated queries by Servoy that are sent to SQL Server, we came to the conclusion that no Database Transactions are started, but instead the “implicit transactions” setting for the current connection is toggled over and over again.  
Mimicking the queries sent to the server in a test script to be ran from SQL Server Management Studio, and comparing this with a SQL statement that uses real named Database Transactions, we observe a difference in the execution time.

We have 2 questions on this:

- why has Servoy chosen to toggle the implicit transactions (“set implicit\_transactions ON/OFF”) instead of using a “Begin tran/commit tran” ?
- How can one avoid using this technique, as we see that in the version with implicit transactions takes about 15-20% more time than the version with explicit transactions?

This implies as well that nested transactions are made impossible.

test script used:

DECLARE @t1 DATETIME;  
DECLARE @t2 DATETIME;

declare @nr\_executions int = 10000;

–implicit transactions “Servoy style”

SET @t1 = GETDATE();

declare @cntr int = 1  
while @cntr \<= @nr\_executions  
begin

SET IMPLICIT\_TRANSACTIONS ON

update search\_criteria\_user set sys\_modificationdate= ‘2017-06-26 14:08:02.047’ where search\_criteria\_user\_id = N’51A41E60-FC45-432F-BEA6-F67B4B836D39’  
IF @@TRANCOUNT \> 0 COMMIT TRAN  
IF @@TRANCOUNT \> 0 ROLLBACK TRAN

IF @@TRANCOUNT \> 0 COMMIT TRAN  
SET IMPLICIT\_TRANSACTIONS OFF

set @cntr += 1  
end

SET @t2 = GETDATE();  
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed\_ms;

– Explicit transactions

SET @t1 = GETDATE();  
set @cntr = 1

while @cntr \<= 10000  
begin

begin tran mytransactionname  
update search\_criteria\_user set sys\_modificationdate= ‘2017-06-26 14:08:02.047’ where search\_criteria\_user\_id = N’51A41E60-FC45-432F-BEA6-F67B4B836D39’  
commit tran mytransactionname

set @cntr += 1  
end

SET @t2 = GETDATE();  
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed\_ms;