Re: Locking and Delay in a Bottleneck
- From: "TheSQLGuru" <kgboles@xxxxxxxxxxxxx>
- Date: Mon, 5 Oct 2009 17:25:12 -0500
what about the tsql variable assignment trick?
update table with (holdlock, xlock, rowlock) set @val = col = col + 1
where ...
that should avoid the gap issue on rollback, and provide proper concurrency
control, right?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@xxxxxxxxxxxxx> wrote in message
news:0tudncsTCaz21VfXnZ2dnUVZ_hqdnZ2d@xxxxxxxxxxxxxxxx
First time I have been called a "joker" Erland! :-) I did miss the
requirement that there be no gaps. But I must ask why that is a
requirement.
Also, OP mentions GL table already having an identity PK. Note that there
is no limitation to the number of identity columns you can have AFAIK.
You should be able to convert SNo to identity, set the value with dbcc
checkident and you should be good to go - assuming you can allow for the
occassional gap which would occur if you do a rollback tran after
inserting and before committing.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9C9AB00AC40E3Yazorman@xxxxxxxxxxxx
Bassam (egbas@xxxxxxxxx) writes:
I developed a financial application having many modules like invoices ,
purchasing , general journal vouchers , etc , about 15 modules (Assets ,
Invoices , Purchasing , etc) are generating a journal voucher
accompanying
the saving transaction , this journal voucher is having a header table
called GJ and a details table called GJDetails
I need to serial the entries in the GJ in a column called SNo
(int-Unique constraint) , since im using a Read Committed transaction
level at client while saving any transaction in those modules , I update
the counter in a separate table for the serial to increase it by one ,
table called GJSerial , Column Called NextSNo
Update GJSerial Set NextNo=NextNo+1
Now the problem is , due to Read Committed Transaction Level , it locks
the NextNo row once updated till the transaction is committed , so all
the modules in different users over the network are having to wait for
any transaction to be committed before they can themselves increase the
GJSerial and start their saving process , some clients are in a remote
sites with slow links , those clients are locking the row in GJSerial
table for the more speed links and cause the entire network to be very
slow sometimes with a timeout errors as they are all waiting for each
other to take the next GJ SNo
With your experience , how can I solve this problem and at the same time
serial the Journal vouchers without serial gaps ?
This is by no means a trivial problem, and there is no simple solution.
Sure, it's not surprising that some joker would suggest that you should
use IDENTITY, but IDENTITY on its own does not solve the problem,
because IDENTITY will give you gaps.
If the business requirement is that the voucher numbers must be
contiguous, this means that when we generate a number, we cannot commit
until we have actually used the number. But if we generate a number
without locking it, multiple processes could use the same number.
One approach is to try to generate the number as late as possible in
the transaction, working with a temporary voucher number in the
mean time. You could generate this as -abs(checksum(newid()). You would
also save this number in your tables. At the end of the transaction,
you generate the real number, and store it together with the temporary
number in a table whereupon you commit. Then you have a separate process
that reads this temporary table and updates the other tables with
the real number. This would more or less call for your FK constraints
to have ON UPDATE CASCADE.
You could also use IDENTITY, and use the identity value as key in
your tables. And at the end of the transaction, you generate the real
number and store it in a table together with the IDENTITY value. This
solution is leaner, since there are no updates. However, you will
probably change a lot of queries; this IDENTITY value must not be
displayed anywhere.
The final solution is to generate the number in a transaction on its
own, where all you do to log it to a table. Then you need to convince
the auditors, that this is enough to show that there is not a gap,
although there is one in the journal. Technically, this is the most
palatable solution, but getting the business to accept it is a
challenge.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: Locking and Delay in a Bottleneck
- From: Erland Sommarskog
- Re: Locking and Delay in a Bottleneck
- References:
- Locking and Delay in a Bottleneck
- From: Bassam
- Re: Locking and Delay in a Bottleneck
- From: Erland Sommarskog
- Re: Locking and Delay in a Bottleneck
- From: TheSQLGuru
- Locking and Delay in a Bottleneck
- Prev by Date: INSERT UPDATE DELETE Trigger
- Next by Date: Re: Help with ROW_NUMBER and recursive query
- Previous by thread: Re: Locking and Delay in a Bottleneck
- Next by thread: Re: Locking and Delay in a Bottleneck
- Index(es):
Relevant Pages
|
Loading
