At 11:51 PM 30/10/2007, you wrote:

>Well, the basic error message is (and only after them the bugcheck
>messages come, so - I hope if I manage to eliminate these, then the
>bugcheck messages would be eliminated well):
>'lock conflict on no wait transaction
>deadlock
>update conflicts with concurrent update'

As Ann commented, this "deadlock" is not related to your original "lock denied" problem. User locks (such as are reported in the message above) are *meant* to happen. Confusingly, the API's messaging system uses the word "deadlock" to group almost all user locking errors, including conflicts (which are not deadlocks). Your case is one such.

>And it can be fairly simply reproduced: user A updates record 1 and
>then user B tries to update the same record 1 and so - user B
>receives this error message, however, user A can continue his work on
>record 1 how long it is necessary for him. And user B can do any
>update on record 1 only after the updates done by A has been
>committed or rollbacked.

Exactly. And this is how it is meant to be. Your application code should intercept this error and send an instruction to the user to cancel this work, because another user has already posted (but not committed) some change to the record. The transaction has "no wait" set for resolving such conflicts, which is why the engine returns the exception, instead of waiting until user A has committed or rolled back his transaction.

>It is pretty clear - but this means, that A is doining very long lasting transactions which can be simply simulated by isql or any other DB tool, but it is almost impossible to do with usual Delphi components.

That's not true. Most Delphi components for InterBase and Firebird support all of the transaction parameter configurations, generally with properties like NoWait (for lock resolution, set true to return immediately, false to wait until the conflicting transaction finishes); RecVersion (a true/false setting for read-committed transactions to say whether a WAIT transaction (NoWait=false) should overwrite the records written by most recently committed transaction); ReadOnly (true for READONLY, false for READWRITE); TransIsolation (one setting available for each supported transaction isolation ReadCommitted, Concurrency, Consistency); and more in some component sets.

Many of the available GUI tools are written in Delphi and you can simulate locking contention situations with multiple instances of them, just as you can with isql. Or, you can have isql and your favourite Delphi-based tool, or an instance of your application, running in parallel if you want to play about with user locking.

>There is transaction with read-
>commited when data are retrieved and another transaction when the
>updated are applied to DB (it is under 1s). So - how it is possible
>with Delphi (ibx) to make long lasting transaction? Are there some
>common failures?

With any Delphi components, you have to be very careful NOT to write applications whose design relies on long-running transactions. The reason for this comes from the original design of Delphi's database access - the old VCL that communicated with the BDE layer, which is the Paradox desktop database engine. Paradox is not client/server and it also doesn't have transaction isolation so the BDE did a lot of really weird things with IB to hide transactions from you and also to encourage you to keep building applications on the desktop model....IBX and some other IB/Fb components stick to this model.

The result is your "typical Delphi app" - a spreadsheet-style desktop display - with a huge SELECT set in a grid, from which you kick off inserts, edits and deletes, all in a single read-write transaction context. To help you with this illusion, Borland invented CommitRetain, which allows your transaction to be committed but keeps all your buffer cursors open on the server - no cleanup between transactions. It's a long, long story that needs careful study and I'm not writing a book today....

>As far as I know - then there read-commited can be
>enabled for select commands, automatic rollback already occures for
>any failure - there is simply not much chance to have long-lasting
>transaction.

Delusion. There is no such thing as "automatic rollback" unless you put it there. If you get a lock conflict exception and you don't handle it, that transaction does not end unless YOUR CODE calls Rollback. If it calls RollbackRetaining, the transactions resources will survive and any obsolete record versions will be unavailable for cleanup by garbage collection. (Same story with CommitRetaining, too.)

You owe it to yourself to find out exactly what your transaction settings via IBX are doing for you (and against you). As a general rule, you *will* have to get past Borland's defaults and your old spreadsheet thinking to deal with the long-running transaction issues (which are many).

>Well - and another question - is there some chances for user B to
>detected, that some other user (e.g. A) is already doing some updates
>on record 1 and so - user B can wait and do his updates later

A WAIT transaction will enable that, but it is not very useful for user B, if you application code allows the other user to keep the conflicting transaction unfinished interminably. With Read Committed isolation, a WAIT transaction can have the ability for user B to overwrite the work committed by user A, so you need to work out whether that is what you want to happen.

>> is there possibility to see, that record is locke by some other user...
>I guess, that it is possible using the commercial IB, where tmp$...
>tables are gather all the accounting information...

Table monitoring is too noisy to use as a way to get a pessimistic lock. A NO WAIT transaction returns an exception on a lock conflict on calling Post. This is slim and elegant. If Post is too late for you, you can engineer a pessimistic lock from the client side by having your Edit and Delete calls attempt a "dummy update". Some Delphi dataset components provide the capability to set a pessimistic lock this way (off-topic here).

Firebird also has the capability to impose a pessimistic lock on records one by one as they are delivered out of the server's buffer, using the SELECT...FOR UPDATE WITH LOCK syntax. You can read about this in detail in the Fb 1.5 release notes - BUT UNDERSTAND AND HEED THE WARNINGS. If you really don't understand much about how transaction parameters work, you should probably go with the dummy update approach.

>And - at last - is there some way to monitor, which is the user of
>type A - who has started some long lasting transaction.

Not in any released version of Firebird so far. In the forthcoming Fb 2.1 (still in Beta), yes. Transaction monitoring is handy during development but, for deployed systems, it is the ambulance at the bottom of the cliff. Don't design multi-user applications that allow users to have long-running read-write transactions, or that rely on CommitRetaining and RollbackRetaining.

./heLen