This topic discusses transaction isolation. Each section in this topic lists a series of steps to demonstrate how different transaction isolation impacts the user of data associated with a particular transaction. To understand each section you should refer to the TransactionPausing tutorial distributed with IBO - which has two datasets, each associated with a different transaction but each refering to the same InterBase table.
One dataset (the "Controlled Query") allows you to specify the isolation of its transaction.
The other dataset (the "Auto Query") is associated with a <default> transaction, which means that the transaction isolation is always set to tiCommitted.
Each dataset has its own grid allowing you to try editing the data.
Only committed changes are EVER visible to other Transactions.
1. Make sure the Controlled Query transaction is set to tiCommitted.
2. Add or edit a record in the Controlled Query Grid and post the changes but do not press any of the commit transaction buttons.
3. Click on the Auto Query Grid and then press the RefreshAll query toolbar button. You will notice that the changes you have made on the left DO NOT appear on the right.
4. Click the Commit Retaining transaction button (which will commit the changes made to the left grid) and then repeat step 3. Now you should see the changes that you made on the left appearing on the right grid.
NOTE: The changes appear in the Auto Query Grid because its Isolation is set to tiCommitted and so it will see any changes that have been committed.
tiConcurrency Isolation takes a "SnapShot" at transaction start
1. Set the Controlled Query transaction isolation to tiConcurrency.
2. Add or edit a record in the Auto Query grid and post the changes. Because the Auto Query default transaction is set to AutoCommit, this will result in the changes being committed.
3. Click on the Controlled Query Grid and press the RefreshAll query toolbar button. You should NOT see the changes you made on the right grid appearing on the left grid. This is because the Isolation of tiConcurrency takes a "Snap Shot" when the transaction is started and will ignore any changes made by other transactions - but notice that this Isolation mode did NOT stop the changes from being made, it just cannot see them. (It is actually looking at "old versions" of the records maintained by Interbase for this purpose - which is what makes Interbase "multi-generational".)
4. If you Commit or Rollback the transaction and reopen the query you should see the changes appearing. (This will not work if you use CommitRetaining.)
tiConsistency Isolation blocks changes
1. Close and restart the application - so I know what state we are in.
2. Start the Controlled Transaction and change its isolation to tiConcurrency then open the Controlled Query (click on the left grid then click on the Open query button).
3. Open the Auto Query (click on the right grid then click the Open query button).
4. Try to delete a record from the Auto Query. You should receive a "lock conflict" exception message.
This should highlight why tiConsistency should be used with great care. It will essentially lock ALL records selected by the transaction and prevent changes to any of these records even if it has no use for them itself. So if you execute a "SELECT * FROM TABLE;" then you have essentially locked ALL records in the table - which is generally an undireable effect.
