Need help on SET IMPLICIT_TRANSACTIONS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help on SET IMPLICIT_TRANSACTIONS

Author

Topic

MuffinMan
Posting Yak Master

107 Posts

Posted - 2001-05-18 : 11:36:16

We have a contractor developing a Netcool/Impact application for our company. The Impact product is designed to create tickets in our customer service request database (Remedy). In order to create a ticket, the following must occur:

1) Get the next ID (Pri Key) from the master ID's table for the service request table
2) Update the next Id on the master table for the service request table
3) Create the ticket

Steps 1 and 2 MUST be done within a transaction to ensure that duplicate records are not created.

Here are the results of the SQL Profile trace I did:


Event         Time         SPID ConnID Text
============= ============ ==== ====== ========================
SqlBatchCmplt 17:17:15.313  166   1926 set transaction isolation level  read committed  set implicit_transactions off 
SqlBatchCmplt 17:17:15.313  166   1926 SELECT name, nextId FROM ARSchema WHERE ...
SqlBatchCmplt 17:17:15.343  166   1926 set transaction isolation level  read committed  set implicit_transactions off 
SqlBatchCmplt 17:17:15.343  166   1926 update ARSchema set NextId = 1366 where ...

According to BOL:
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
Also:
In autocommit mode, all individual statements are committed if they complete successfully.

So, as I see it, since implicit transactions are OFF, SQL Server treats each successfully completed statement as a transaction.

Thus, it appears to me that another process could do steps 1 and 2 WITHIN an explicit transaction (Begin Tran Select NextId... Update nextId... End Tran) in between the time the Impact application completes it's second statement (the Update).

This would cause the Impact application's connection to throw an error because nextId MUST be unique for a given table in the Master ID's table (arschema).

Am I interpreting this correctly?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources