At 11:01 PM 11/06/2006, you wrote:

>I've locked the some rocords in my application by using explicit lock and it
>is no problem, but I would like to know who was locked this those records?
>so I kept user information into some fields on locked rocords, but other
>transactions cannot see this value, I know this transaction is not committed
>it seems DirtyRead is needed for this.
>
>I've used dbExpress with Delphi to write this application and I've tried to
>specified a DirtyRead(ReadUncommitted) isolation for starting a transaction
>but there is no effect, for specificed this value with a connection
>It's work,

No, it doesn't work. DirtyRead is not available under any circumstances.

>but I need to use this isolation with transaction level not a
>connection level
>because of all transaction depenced on this connection is also toke a
>ReadUncommitted effect

The problem of having only one transaction to work with is a
shortcoming of DBX, not Firebird. DirtyRead is *NOT* a feature. It
is described, not ascribed to!

>, how can I do? or Which other way to do this?

Search your soul very vigorously and try to find a good reason to
want to use an explicit lock *at all* and then, next, what conditions
you are creating that require you to find out who has such locks. If
it is because your application code is allowing a transaction to hold
uncommitted work for long periods, then fix your application code.
DBX does not give you the benefit of read-only transactions so it
absolutely obliges you to modularise *every* task and, through your
code, to force a commit or rollback at the earliest possible moment.

Perhaps you don't understand that pessimistic locking is unnatural to
the way Firebird deals with transaction isolation. It has optimistic
row-level locking on all reads. This means it does not lock anything
until a request arrives to change a row. At that point, the changed
row becomes unavailable for writes by any other transaction; but all
of the unchanged rows can be accessed by other transactions.

You really must take notice of the warnings in the release notes
about using WITH LOCK and never use it for SELECTs that return multiple rows.

If you have no choice but to use DBX, then make extremely thorough
use of the exception mechanisms in Delphi.

Others will probably suggest ways you can implement a "gateway" table
that users of your application can use to write and commit a "signal
of intention" each time a specific transaction (CURRENT_TRANSACTION)
decides to "lock" a row in a table (TABLENAME + PRIMARY KEY NAME +
PRIMARY KEY VALUE) and finally updates a "FINISHED" field when the
work is done. All transactions that intend to lock a row must read
this table and abandon their intentions for that row if it is present
and the FINISHED field is null.

I won't go there. It might be useful for a particular table that
requires work to be strictly serialised, but it's unnatural and
costly for normal work and it falls apart if you have misbehaving users.

Don't deceive yourself that working in a transactional database is
like working with Access, which does not support transaction isolation at all.

./heLen