>Please help my understand what will happen in the following cases involving >transactions. >System: Delphi XE2, Firebird 2,5x, Using DBX components. Isolation level: >ReadCommitted. > >The actual statements are much more complex and involves various statements in >the transaction and/or triggers that updates/insert into up to 8 tables. > >Case 1 >App 1 starts a transaction (T1) and in an After Insert trigger for MasterTable >do something like: >Update TableSub set X = X + MasterTable.X where Y=Y.MasterTable >Before App1 commits, App2 starts a transtaction (T2) and does the same >statement. Then App1 commits(T1) and then App2 commits(T2). >Will this result in a deadlock or will T1 "run" and then T2 or will X.old in >both transaction have the same value?
You will get a lock conflict on T2 (a deadlock involves several tables where T1 waits for T2 and T2 waits for T1, your situation is just T2 waiting for T1 and hence just a lock conflict). >Case 2 >Same case as above but the trigger use X = GEN_ID(Generator1,1). >Will X be, for example 10, in T1 and 11 in T2, or will it result in a >deadlock, or will X be 10 in both transactions? Generators are not transaction bound and you would get different numbers for T1 and T2. >Any information will be appreciated and even more so hints as on how to handle >this. I would like T1 to start and run to completion and then T2 taking >into account what happen in T1. The only way to make T2 take into account what has happened in T1 is for T2 reading the data after T1 has committed and only then do the changes. Sometimes concurrency issues can be avoided by using INSERT rather than UPDATE and then use SUM whenever you have to read the value. HTH, Set
