Re: Re: [firebird-support] update all records

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

 
, liviusliv...@poczta.onet.pl [firebird-support] responded
 
 
one of possible reason is that records are not visible to transaction with this 
update statement.
e.g.
1. you start transaction (tr1)
2. someone start transaction 2(tr2)
3. tr2 insert some records
4. tr1 run update
5. tr2 commit
6. tr1 commit
all records inserted by tr2 are not updated if tr1 is e.g. snapshot
or during tr1 update there are inserts.
 
Actually, that's not true.  If tr2 inserts records before tr1 attempts its 
update, tr1 will discover an update conflict when it finds one of tr2's new 
records.  If tr1 is a no-wait transaction, it will report an error immediately 
otherwise it will wait for tr2's commit.  Then, if tr1 is a snapshot 
transaction, when tr2 commits, tr1 will report an error.  If tr1 is a 
read-committed transaction, it will silently update tr2's new records without 
having looked at them.*
 
If, however, the sequence is that both transactions start, then tr1 does its 
update and tr2 stores its new records after the update completes, both 
transactions will succeed and the records inserted by tr2 will not be updated.  
Which would also be the case if tr1 ran to completion before tr2 started.
 
What's slightly more likely is that tr1 and tr2 are concurrent and the sequence 
of actions is that tr1 modifies all the stock records then tr2 reads all the 
stock records and sees the old versions (assuming that tr2 is not a "no record 
version" transaction).  Even if tr2 is a read-committed transaction, it will 
read the next older version of records if the most recent is not committed.
 
So, Sergio, a bit more information would help us give you a better answer.  Are 
there multiple transactions running?  Why do you think some records weren't 
updated?  Do you know what transaction options you're using?
 
Cheers,
 
Ann
 
_,___
 
Hi Ann,
 
this is not true.
 
CREATE TABLE TEST
(ID INTEGER);
 
commit;
insert into test(ID) VALUES(1);
insert into test(ID) VALUES(2);
insert into test(ID) VALUES(3);
insert into test(ID) VALUES(4);
insert into test(ID) VALUES(5);
commit;
 
1. start snapshot transaction tr1
2. start snapshot transaction tr2
3. in tr2 insert record
insert into test(ID) VALUES(10);
4. in tr1 update all records
update test set ID=ID + 100;
5. commit tr2
6. commit tr1
 
 
you got no error and table with data:
ID
101
102
103
104
105
10!!!
 
tested with flamerobin (default flamerobin settings: transaction isolation 
"CONCURENCY ISOLATION MODE" +WAIT FOR LOCK RESOLUTION) + FB3
 
regards,
Karol Bieniaszewski