Isolation Level, generating sequences

  • Thread starter Thread starter MarkH
  • Start date Start date
M

MarkH

We're using ADO.NET and SQl Server 2K.

I'm updating an integer column in a seperate table which I use to hold
sequence numbers for different reference strings.

Together the string and int form a unique code which is we use as user
reference for invoices, orders etc, (the PKs are hidden).

Before I enter a new invoice we:
1) open a transaction in ADO.NET,
2) call a stored proc to increment the relevant sequence and return the
new value,
3) insert data into the invoice tables (including the unique code from
step 2)
4) commit / rollback the transaction

At the moment we're using the default Isolation
Level, is this safe, or do we need to increase it?
 
Yes, it's safe, but you're making more calls to the server than you
should be. Create one stored procedure that implements an explicit
transaction, and inside of the transaction perform all of the
operations. The way you're doing it now relies on two round trips to
the server in order to complete the transaction, which increases
network and server load managing locking resources while slowing down
your application.

--Mary
 
Back
Top