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