At 10:16 PM 31-08-01 +0000, you wrote:

>i don't know delphi, and i don't know IBO, i'm just an
>IB_SQL user, but hopefully someone can help anyway.
>
>we've been having a problem using IB_SQL (aka IB_WISQL).
>all of our application clients (in perl and in C) are using
>read committed, record version, no wait.
>as long as ib_sql is not in the picture, everything is fine.
>
>however, as soon as one of us starts using ib_sql, then
>we start getting these errors:
>-lock conflict on no wait transaction
>-deadlock

Are the errors returning to the Perl/C clients or to IB_SQL or both?

>this is what i would expect *if* IB_SQL were setting more
>aggressive transaction options, such as snapshat
>(aka "concurrency" aka "repeatable read").
>
>but we *are* setting the transaction isolation in IB_SQL to
>"read committed", with LockWait not checked.
>in theory this should match what our other clients are doing.
>but perhaps the cursor window of ib_sql is not using those
>transaction options?

The cursor window uses whatever transaction isolation is set on the transaction tab.
Are you watching it in the SQL Monitor window?

>or maybe there is some other bug?

What is an IBSQL client typically doing when the lock conflict occurs? Like, what statements are you submitting to the cursor dialog? Are you getting a dataset back? and, if so, do you hit the EOF button to fetch all rows?

What type of statements are your Perl/C clients submitting?

Are you using pessimistic locking on a select * from table for update, for example?

>unfortunately interbase has essentially no server-side monitoring
>and control capabilities, so it is difficult to say for sure.
>but we definitely only have this problem when using ib_sql.

Run the monitor and you will at least know what the ib_sql client is doing...

>oh, and what is the difference between "AutoCommit"

Autocommit is a client-side trick that places the transaction under implicit transaction control. When Autocommit is true, the physical transaction is implicitly started when the dataset goes into an editing mode (i/u/d) and that transaction is committed when the client calls the dataset's Post method.

Calling StartTransaction disables Autocommit and the dataset won't be updated on the server until the client calls Commit. The task of Post under this condition is to send edits to the server and queue them up until either COMMIT makes them permanent (replaces the record version) or ROLLBACK cancels them altogether.

>and
>"Server AutoCommit"?

This parameter can be passed to the server when the transaction must be committed immediately on the server if the statement executes successfully, without waiting for the client to call Commit. Typically you would use it when executing a script that affects metadata, or when executing a stored procedure or dsql whose effects you want clients that are in read committed isolation to see immediately. It's also useful when you want to execute one of the mentioned tasks and be alerted to any exceptions before continuing with another task.

Think of this type of transaction as "hit and run". It's not for use under regular conditions where you want control of the transaction to stay with the client.

btw, you can't combine pessimistic locking with ServerAutoCommit because the "locking" statement, e.g. update atable set id=id, would be committed immediately and thus never be able to sustain the intended exclusive access condition for the rest of the transaction.

rgds
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________