thank you Ann for this indepth answer. It is REALLY appreciated and of great vallue.

Have a nice day

Johannes
At 06:30 PM 20/06/2006, you wrote:

>Johannes Pretorius wrote:
>>
>> 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 ?
>
>I'm not sure I understand what your asking, but let me explain what
>I do understand. When a transaction - and all operations in Firebird
>occur in transactions - inserts a record, the record is placed on a
>database page in the server cache. The record as a header that
>includes the transaction id of the transaction that inserted it.
>
>When the cache page is written to the database, the record goes
>with it. The page may be written for one of several reasons. One
>is that the transaction that inserted the record commits. Before
>the commit completes, all changes made by the transaction are
>written to disk. Another is that a different transaction wrote
>a record on the page and that transaction committed, causing the
>new page to be written with all the changes on it - committed or
>otherwise. A third reason is that the cache is full and that
>page is the least interesting, so it gets flushed to disk.
>
>So, a record in the database may be committed or not. The server
>keeps track of the state of "interesting" transactions. When a
>transaction asks to read a record, the server checks whether the
>record was created by a transaction whose results are legitimate
>for the current reader. The definition of "legitimate" depends
>on the type of transactions. A concurrency transaction sees only
>records that were committed when it started. A read-committed
>transaction sees all committed records. If the record is OK,
>the system returns it. Otherwise, it checks for older versions.
>For a newly inserted record, there are no older versions, so
>nothing is returned.
>
>When a transaction updates or deletes a record, it creates a new
>version on a data page in the cache. The new version is stored
>on the same page as the previous version. If there's room for
>both, the old and new version are on the same page. If not, the
>old version is moved. The page can be written from cache for
>any of the reasons above.
>
>When a transaction tries to read a record that's been updated
>or deleted, the system checks to see if the new version is
>legitimate for the current transaction. If not, it checks
>the next older version. If that's not legitimate, the system
>checks the next older, etc., returning the first legitimate
>version or nothing.
>
>When a transaction tries to update a record, the system first
>checks that the most recent version of that record is legitimate
>for that transaction. If there are changes to the record that
>the updating transaction can not see, the update fails with an
>error like "Deadlock - update conflict".
>
>
>>
>> The following is based on a transaction with NO Rec Versioning.
>>
>> 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.
>
>That's less obvious than you might think. If client A is a
>concurrency transaction, then it sees a consistent snapshot
>of the database showing the committed data when A started.
>If A runs the same query twice, it gets the same results.
>That's considered "correct" in database circles. One reason
>is that B may fail, and since B's transactions are atomic,
>all its change disappear. In database circles, it's
>considered quite "incorrect" to read data that later
>disappears.
>
> > 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.
>
>Assuming that A is running a single transaction in
>concurrency mode, A will get an update conflict error
>when it tries to update a record that B has deleted.
>If A is running a single transaction in read-committed
>mode and B has committed, the system will recognize that
>the record has been deleted and A will not get a chance
>to update it. If B has not committed, A will get an
>update conflict error.
>
>> WHAT will the effect be on this , regarding indexes ?
>
>Indexes follow the same rules.
>>
>> 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 ?
>
>A concurrency mode transaction cannot "refresh" a query -
>the rules say that a query gets the same results every time,
>regardless of what else is going on. You can "refresh"
>results of a read-committed transaction, and, assuming
>that all changes made by other transactions are committed,
>you can then update those results.
>
>No transaction can overwrite the changes made by a
>concurrent uncommitted transactions, nor can any
>transaction overwrite changes it cannot read.
>
>
>
>Regards,
>
>
>Ann
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>