Good day Adam
=-0-=0=-0-=-0==-0

Thank you very much for your answer and help. It was REALLY helpfull.

I will also remember in the future to rather start new message (I use an e-mail client, thus dont see the effect
like a new reader).

Thanks once again and have a nice day

Johannes

At 01:59 AM 21/06/2006, you wrote:

>--- 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
>
>
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>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
>
>
>
>