[DUG] MSSQL Question

[Neven MacEwan]

Kyley

> Thanks Neven,
Cheers
>
> 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.
Funny how we obsess over the details isn't it
Read the manual on TRANSACTION ISOLATION, it will give you a good idea
>
> 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.
 I don't think you'd get a deadlock for a second or two its more 
deterministic than that, if you do get a locked resource
(which can only really happen if in a trans a locks table a and then 
table b and trans b locks them in reverse order, and they collide) then 
the second trans fails

I tend to make my databases transactional (with accruals in triggers) 
and then you a) dont really require transactions and b) eliminate the 
chances of a deadlock as the table update sequence is controlled
by the triggers and hence is consistent

Neven