Sergio asked why this statement
update stock set actualizar = 0 where 1=1
doesn't update all records in the stock table. First, the clause "where
1=1" is unnecessary. The update statement does not requires a where clause
and in the absence of a where clause, it affects all records in the table.
, 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
* If the inserts and update are running at the same time, the most likely
case is that tr2's inserts will be physically the last entries in the
table. Tr1's updates will be made in storage order, so the last thing
records it tries to modify will be the records tr2 created. If the table
has had lots of records deleted, it's possible (I think) for tr2 to store
its records on partially empty pages in the first part of the table and
avoid a collision with tr1 if tr1 has already modified the records if found
on those pages. That's very similar to having tr1 perform its update
before tr2 stores its records.
