[DUG] MSSQL Question
David Brennan
dugdavid at dbsolutions.co.nz
Mon Dec 1 15:54:53 NZDT 2008
Note also that once the first transaction gets its next key value and
updates the key table that should mean that the second transaction will get
blocked and is unable to get its next key value until the first has either
committed or rolled back to release the lock on the key table. This is fine
so long as each transaction is fast. However if your transactions are slow
(or some may be slow) then this could be a problem in which case
pre-fetching keys for all the records you want to insert is a good idea.
The Deadlock problem can occur if you aren't careful. For example if this
happens then you have a problem:
Tran 1:
GetNextKey('Client')
GetNextKey('Invoice')
Tran 2:
GetNextKey('Invoice')
GetNextKey('Client')
If both of these transactions manage to get their first keys at the same
time and then go to get their second key values... you have deadlock. #1
wants an Invoice ID but can't get because #2 has it locked, and #2 wants a
Client ID but can't get it because #1 has it locked.
Pre-fetching key values is an easy way around this. The other solution is to
always get your key values in the same order (ie always get Client before
Invoice) so that the locks are always applied in the same order and only one
transaction can be winning at any point in time.
Of course you may well already be aware of all this but it seemed pertinent
to the discussion.
Cheers,
David.
From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Myles Penlington
Sent: Monday, 1 December 2008 3:36 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL Question
With MS SQL, only the top level commit/rollback matters, so to get real
sequential numbers you would have to do this in a initial call/transaction
to the db, before you perform the rest of the updates in a second
transaction, else in case of a rollback, the allocate key update would also
be rolledback.
The nested transactions in SQL are basically present to handle nested stored
procedure calls that deal with transactions.
Myles.
From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Kyley Harris
Sent: Monday, 1 December 2008 3:17 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL Question
Thanks Neven,
I think your idea of keeping the key fetching in a seperate transaction is
the most likely to prevent any locks happening. Key fetch speed is not a
huge issue. This is very easy to implement in my middle layer. My issue is
then that I have a few triggers that insert data that call on a sequence
number.. if the server code that initiated this rollsback a transaction,I
guess my worst case scenrio is simply a deadlock on a table for a second or
so..
Thanks.
Now admittedly, I am just trying to be sure, because I'm still not familiar
with the in's and outs of MSSQL and how it locks.
Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part
of software failure to protect the database. Our average transaction time is
less than 100ms, generally about 5-10ms as we pre-do everything in OO and
then commit at the end of each area in a short fast swoop in a middle tier.
This tends to eliminate lock contention, I just want to cover all the bases
and make sure the keys don't rollback.
On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan <neven at mwk.co.nz> wrote:
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
> Hi Neven,
>
> Currenlty I am using a stored proc that accesses a table in the form of
>
> (
> SequenceName varchar(100);
> KeyValue Int
> )
>
> I'm using update locks etc..
>
> my worry is that
> Transaction A asks for "ClientNO" 1-3 keys
> Transaction B asks for "ClientNO" 4-6 keys
>
> A Rolls Back
>
> Transaction C or d or e in the future may get 4-6 taken by B again
> because of A's Rollback.
>
> The keys are not used for Primary or Relational anything. I use
> UniqueIdentifier for that.. The sequences do not need to be perfect,
> gaps are allowable.. these sequences are for Data Values used by the
> software users.. ie Batch No, Client No, Debtor No, Statement No,
> Invoice No.. etc etc. and these numbers are then used for quick
> referencing of information..
>
> in interbase and Oracle.. its very easy..
>
> have to pop out for an hour.. Thanks if anyone has any ideas for
> making sure I don't get lock contention, or reissuing of the same
> number twice.
>
> On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan <neven at mwk.co.nz
> <mailto:neven at mwk.co.nz>> wrote:
>
> Kyley
>
> Why not just use a stored proc as a 'generator' or a high/low
> sequence,
> I take it that you want to know the surrogate PK Value?
> Must they be sequential?
>
> Neven
> > pretend IDENTITY columns dont exist, because they don't do what I
> > want, which is to create non-rollback numbers like IB Generators or
> > Oracle Sequences.
> >
> > No matter how much rowlocking, updatelocks etc.. if a rollback
> happens
> > then the Sequence numbers can get scrambled depending on transaction
> > order.
> >
> > I've read under SQL 2008 (im using '05) that you can create a remote
> > stored procedure using a loobback connection, where the remote call
> > will then commit the increments outside of the local transaction
> > running? Can anyone confirm this?
> >
> > My only other thought is to write a CLR (when I learn how) that uses
> > critical sections and a seperate database connection to access
> and do
> > all the commits to the Sequence generation table..
> >
> > any thoughts comments appreciated.
> >
>
------------------------------------------------------------------------
> >
> > _______________________________________________
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
>
>
>
> --
> Kyley Harris
> Harris Software
> +64-21-671-821
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe
--
Kyley Harris
Harris Software
+64-21-671-821
Attention:
This communication is confidential and may be legally privileged. If you
are not the intended recipient, please do not use, disclose, copy or
distribute it, other than to return it to us with your confirmation that it
has been deleted from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081201/ca33c364/attachment-0001.html
More information about the Delphi
mailing list