Dear Helen,

Thanks for detail explain!

My problem is that when a detail was edited and posted in a
Transaction, the second user still can't to read "latest commited"
version of the record. When LockWait=True, the application of second
user will be hold to wait the transaction of first user to commit or
rollback; when LockWait=False, the application of second user will
raise record locking error.

I want to allow another user can read the record which was edited and
posted in a uncompleted transaction without waiting or error.

Thanks for help!

Earnest

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 01:00 PM 20/03/2008, you wrote:
> >Hello, I have a record locking problem needing your help!
>
> I am using Delphi 2007, IBO 4.8.7, IBOQuery, AutoCommit=True,
> Isolation=tiCommitted.
>
>
> >I have master/detail tables to record sale invoice. I call
> >IB_Database.StatTransaction before editing master reocrd. When one
> >user is editing a record of detial, other user can't read the
edited
> >record, system will wait until the record has been cancelled or
> >posted.
>
> Transactions cannot see work that other transactions are doing.
Fb/IB do not support "dirty read" so, even when the first transaction
(A) posts the work that results from an Edit, other transactions will
not see those changes. If the other transactions are viewing the
table with ReadCommitted isolation, they will see the changes of
Transaction A only after Transaction A has committed. If Transaction
A does not commit, but rolls back instead, other transactions will
NEVER see those changes.
>
> Whilst TrxA is reading the record (XYZ), other records can read
the "latest committed" version of that record.
> --- during Editing, nothing changes on the database side
> --- at Post, the dataset sends a DML statement to the server. If
no other transactions already have uncommitted changes posted for
that record, Trx A gets the lock. Other transactions can still see
the latest committed version, as before, but none will be allowed to
post changes to that record.
>
> If TrxA actually INSERTs the record in question, it does not exist
for other transactions to see until that INSERT is committed by
TrxA. If it is true that you have other users that cannot see ANY
version of record XYZ while TrxA is Editing it, then the reason will
be that TrxA itself inserted the record.
>
> >Is there any way to allow the second user to view the edited
record
> >without waiting the first user completed editing?
>
> No.
>
>
> >I read the book of Helen: The Firebird Book. I found that in page
> >552: In Firebird, all updates are at row level ... When a
transaction
> >submits a request to update a row; the old version of that row
> >remains visible to all transactions. Writers do not block readers.
> >
> >Is there anything I missed?
>
> Editing <> Updating. Perhaps you are confusing "editing" (which is
a client-side, i.e. Delphi operation on a record in the client
buffer) with "updating" (which is a DML operation on a record in the
transaction's view of database state).
>
> --- you can start an Edit and Cancel the edit. Nothing happens on
the database side. Cancel does not cause a TrxA to end.
> --- once an edited record is Posted (successfully), a new record
version is written to the database, but is visible only to TrxA.
Now, the "Edit" cannot be cancelled.
> --- a Posted record version will be made permanent in the database
when it is successfully committed by committing TrxA; and it will be
undone if TrxA is rolled back.
>
> You can set the PessimisticLocking flag of the dataset. In this
case, before going into an editing mode (edit, update or delete) the
dataset will try to post a dummy update to the server. The effect
will be EITHER
>
> -- the dummy update succeeds and TrxA gets the lock
>
> OR
>
> -- the dummy update fails due to another transaction already having
a lock on that record and the dataset will not be allowed to go into
the requested editing mode.
>
> This can be useful if you don't have users leaving uncommitted work
around for long times, as it prevents two users from trying to edit
the same record at the same time. It does not somehow magically tell
other users what is going on in TrxA.
>
> Perhaps also you don't understand that Autocommit behaviour is
overridden by starting the transaction explicitly with
StartTransaction. You must COMMIT or ROLLBACK the transaction
explicitly in order to end the transaction.
>
> And maybe you are not aware that a ReadCommitted transaction needs
to refresh the dataset to see the committed changes of other
transactions.
>
> Helen
>