--- In firebird-support@yahoogroups.com, Johannes Pretorius
<johannes@...> wrote:

>
> Good day Everybody
> =-0-=-0=0=--0=-0=-=0
>
> I have been thinking about rec versioniong and transactions and am I

correct to

> say that new records of the data is retrieved after a transactions is
> commited not when started ?

Hello Johannes,

Firstly, it is important to start a new message rather than reply to a
previous post when starting a new topic. Most newsreaders will file
your question as a response to someone elses question, so many people
will never read this.

In answer to your questions, I think you will confuse yourself if you
think about record versions in terms of 'when new records are retrieved'.

The easiest way to think of it is that a record is considered when you
issue a DML statement (select/update/delete/etc). Each DML statement
is run in the context of a transaction. The settings and 'age' of that
transaction define whether a given record is ignored by any DML.

When you insert, update or delete a record, a new version is placed on
the data page. That version contains the transaction number that wrote it.

A transaction can always see any changes made earlier in the same
transaction.

Before your transaction commits, NO other transaction will 'see' your
record version. If it was an insert, they will not see any record, if
it was an update or delete, they will see an old version. The same
thing will happen if your transaction is rolled back.

This is known as isolation, where each transaction is almost totally
'unaware' of other transactions.

If your transaction commits, the changes you made to the database
become public property so to speak.

ANY transaction starting AFTER the moment in time you commit will see
all the changes you made. If you had inserted a new record in the
committed transaction, this new transaction will see it. If you had
updated a record, this new transaction will see the new values. If you
had deleted a record, this new transaction will never know it existed.

What happens to transactions starting BEFORE the moment you commit the
first transaction after the first transaction commits depends on the
settings of those transactions. There are two options. Read Committed
means that they can see those new records from the moment the first
commits. Snapshot means that even though the first transaction is
committed, the other transaction will pretend it isn't and not tell
you about it.

>
> The following is based on a transaction with NO Rec Versioning.
>

There is no such thing as dirty reads in Firebird.

> Then also what I want to know is what happens in this scenario. We

have a client (A) that connects

> to the database and has query on a table (tbl_x). Then we have

another client (B) that runs deletes in intervals on the

> same table. obviously client B's version of the data is correct most

of the time. Now client A does updates to tbl_x every now and

> again thanks to certain factor. If the time has come for A to start

it's update on tbl_x. But now the version is incorrect as some records
has been

> delete from the table by B that A is not aware of. WHAT will the

effect be on this , regarding indexes ?

Regarding indices, they contain all information even for uncommitted
work and deleted work. When you run the query, Firebird will
automatically decide whether B can see a given record based on the
transaction number that wrote the record version according to the
above explanation. That is why select count(*) can't just count the
number of records in an index but must visit each data page.

The worst case scenario for you here is that A runs an update, but
before it gets a chance to commit, B attempts to delete it. How this
is handled depends again on your transaction settings.

A is allowed to make the update unless another transaction that
modified that record wasn't committed at the time A started

B does this same check, but finds that it is not allowed to make the
change.

B is either a WAIT or NOWAIT transaction, which is optimistic and
pessimistic respectively. If B is a WAIT transaction, then it is
hoping that A will rollback. If that happens, B will proceed. If A
commits, then B will receive an exception, something like:

Statement failed, SQLCODE = -913

deadlock
-update conflicts with concurrent update

You want to be careful with such an optimistic approach, because if B
will 'freeze' until A commits or rolls back. If you have a UI waiting
for the query, this may be a problem.

The WAIT transaction is the pessimist. It has the line of thinking
that A will probably commit and I won't hang around for a rollback. If
this is the case, then B will receive an exception immediately.

Note that an exception undoes the current operation, so if you issue a

delete from tableA

and just one record gives such an error, then the entire delete will
be undone. Although previous operations made by your transaction will
remain. If this is a problem for your logic, then you may need to
write a stored procedure to cycle through and delete each record
individually, disregarding such exceptions. Then most of the records
will be removed except for the uncommitted ones that were updated.

>
> Am I correct to say in the above sceanario that one must rather do a

transaction start and commit , before one refreshes one's query ?

The idea of a transaction is that it encapsulates the unit of work you
are doing. You should explicitly start the transaction at the moment
you do the work, and explicitly commit once finished. You should not
just leave the transaction hanging around between these different
units of work, that goes against the principle and will give you big
problems.

Adam