<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-NZ link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>The Deadlock problem can occur if you aren’t careful. For
example if this happens then you have a problem:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Tran 1:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> GetNextKey(‘Client’) <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> GetNextKey(‘Invoice’)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Tran 2:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> GetNextKey(‘Invoice’)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> GetNextKey(‘Client’)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Of course you may well already be aware of all this but it
seemed pertinent to the discussion.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Cheers,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>David.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>Myles Penlington<br>
<b>Sent:</b> Monday, 1 December 2008 3:36 p.m.<br>
<b>To:</b> NZ Borland Developers Group - Delphi List<br>
<b>Subject:</b> Re: [DUG] MSSQL Question<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>The nested transactions in SQL are basically present to handle
nested stored procedure calls that deal with transactions.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Myles.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>Kyley Harris<br>
<b>Sent:</b> Monday, 1 December 2008 3:17 p.m.<br>
<b>To:</b> NZ Borland Developers Group - Delphi List<br>
<b>Subject:</b> Re: [DUG] MSSQL Question<o:p></o:p></span></p>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Thanks Neven, <o:p></o:p></p>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<div>
<p class=MsoNormal>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.. <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>Thanks.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>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.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>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. <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal>On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan <<a
href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>> wrote:<o:p></o:p></p>
<p class=MsoNormal>Kyley<br>
<br>
Firstly if you put the SequenceNo stored proc 'in' the transaction,<br>
don't you already run the risk of deadlock? (as you<br>
can't be sure of the updates the client procedures table updates and order)<br>
<br>
So to avoid this you could 'pre' fetch the keys in a separate trans, or<br>
post fetch them, depending on where the biggest 'risk' of rollback is<br>
<br>
ie for statements (pre fetch)<br>
<br>
x = no of statements to produce<br>
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit<br>
Begin trans, Generate statements using keys, commits/rollback<br>
<br>
Worst case you will 'lose a sequence'<br>
<br>
but since there is almost no chance of a fail on exec FetchKeys then I would<br>
<br>
if (x = Begin trans, Generate statements, null keys, commits == commit) {<br>
repeat {<br>
Begin trans, GenerateKeys(x), commit/rollback<br>
} until commit<br>
apply keys to statements<br>
<br>
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you<br>
will have sole access to the table and as there is only<br>
one table involved I cannot see a huge overhead<br>
<br>
HTH<br>
Neven<o:p></o:p></p>
<div>
<p class=MsoNormal><br>
> Hi Neven,<br>
><br>
> Currenlty I am using a stored proc that accesses a table in the form of<br>
><br>
> (<br>
> SequenceName varchar(100);<br>
> KeyValue Int<br>
> )<br>
><br>
> I'm using update locks etc..<br>
><br>
> my worry is that<br>
> Transaction A asks for "ClientNO" 1-3 keys<br>
> Transaction B asks for "ClientNO" 4-6 keys<br>
><br>
> A Rolls Back<br>
><br>
> Transaction C or d or e in the future may get 4-6 taken by B again<br>
> because of A's Rollback.<br>
><br>
> The keys are not used for Primary or Relational anything. I use<br>
> UniqueIdentifier for that.. The sequences do not need to be perfect,<br>
> gaps are allowable.. these sequences are for Data Values used by the<br>
> software users.. ie Batch No, Client No, Debtor No, Statement No,<br>
> Invoice No.. etc etc. and these numbers are then used for quick<br>
> referencing of information..<br>
><br>
> in interbase and Oracle.. its very easy..<br>
><br>
> have to pop out for an hour.. Thanks if anyone has any ideas for<br>
> making sure I don't get lock contention, or reissuing of the same<br>
> number twice.<br>
><br>
> On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan <<a
href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a><o:p></o:p></p>
</div>
<div>
<div>
<p class=MsoNormal>> <mailto:<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>>>
wrote:<br>
><br>
> Kyley<br>
><br>
> Why not just use a stored proc as a 'generator' or a
high/low<br>
> sequence,<br>
> I take it that you want to know the surrogate PK Value?<br>
> Must they be sequential?<br>
><br>
> Neven<br>
> > pretend IDENTITY columns dont exist, because they don't
do what I<br>
> > want, which is to create non-rollback numbers like IB
Generators or<br>
> > Oracle Sequences.<br>
> ><br>
> > No matter how much rowlocking, updatelocks etc.. if a
rollback<br>
> happens<br>
> > then the Sequence numbers can get scrambled depending
on transaction<br>
> > order.<br>
> ><br>
> > I've read under SQL 2008 (im using '05) that you can
create a remote<br>
> > stored procedure using a loobback connection, where the
remote call<br>
> > will then commit the increments outside of the local
transaction<br>
> > running? Can anyone confirm this?<br>
> ><br>
> > My only other thought is to write a CLR (when I learn
how) that uses<br>
> > critical sections and a seperate database connection to
access<br>
> and do<br>
> > all the commits to the Sequence generation table..<br>
> ><br>
> > any thoughts comments appreciated.<br>
> ><br>
> ------------------------------------------------------------------------<br>
> ><br>
> > _______________________________________________<br>
> > NZ Borland Developers Group - Delphi mailing list<o:p></o:p></p>
</div>
</div>
<p class=MsoNormal>> > Post: <a
href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a
href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><o:p></o:p></p>
<div>
<p class=MsoNormal>> > Admin: <a
href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> > Unsubscribe: send an email to <a
href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><o:p></o:p></p>
</div>
<p class=MsoNormal>> <mailto:<a
href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>>
with Subject: unsubscribe<o:p></o:p></p>
<div>
<p class=MsoNormal>><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<o:p></o:p></p>
</div>
<p class=MsoNormal>> Post: <a
href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a
href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><o:p></o:p></p>
<div>
<p class=MsoNormal>> Admin: <a
href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a
href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><o:p></o:p></p>
</div>
<p class=MsoNormal>> <mailto:<a
href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>>
with Subject: unsubscribe<o:p></o:p></p>
<div>
<div>
<p class=MsoNormal>><br>
><br>
><br>
><br>
> --<br>
> Kyley Harris<br>
> Harris Software<br>
> +64-21-671-821<br>
> ------------------------------------------------------------------------<br>
><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<br>
> Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
> Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi"
target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>
with Subject: unsubscribe<br>
<br>
_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>
with Subject: unsubscribe<o:p></o:p></p>
</div>
</div>
</div>
<p class=MsoNormal><br>
<br clear=all>
<br>
-- <br>
Kyley Harris<br>
Harris Software<br>
+64-21-671-821<o:p></o:p></p>
</div>
<p class=MsoNormal><span style='font-size:7.5pt;font-family:"Verdana","sans-serif"'>Attention:<br>
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.</span><o:p></o:p></p>
</div>
</body>
</html>