--- In firebird-support@yahoogroups.com, "robertz82" <RobertZim@g...>
wrote:

>
> Hi @All
>
> I've got a problem with deadlock-handling. I'm writing a small
> interface for Firebird access using C++ and ODBC. I'm new at database
> programming and firebird. Hope you'll understand my problem =)
>
> The situation:
>
> User one posts a statement like this
> "update table set field = 'XXX' where pk = 1"
> ... and don't commits because he's running a transaction and has to
> do a little more stuff that has to be "rollbacked" if anything fails.
>
> User two is just browsing around and has to edit one of the records,
> user one has updated before. In C++ using ODBC I use:
>
> Recordset.Edit()
> // [...] Set field values [...]
> Recordset.Update()
>
> to edit and save a record. The problem is: Recordset.Update() always
> returns AFTER user one commits or rollbacks his transaction. This is
> simply too late. Is there any way to define a deadlock-detection
> delay and force Update() to return ?

You are connecting using a 'WAIT' transaction. With this type of
transaction, if you try and modify a record that another transaction
has modified since your transaction started, then your query will WAIT
around pending the first transaction commit or rollback. If the first
transaction rolls back, your second transaction will succeed,
otherwise you eventually get the exception.

There is also NOWAIT. NOWAIT will fire an exception instantly, even
though there is the chance that transaction 1 may rollback and
transaction 2 could have been successful.

>
> BTW: Optimistic locking simply suxx in most cases. Is there any way
> to implement pessimistic locking in Firebird ?

That is a bit of an arrogant statement. All it does is bring up
visions about the style of coding you use. There are cases where it is
not ideal, but there are also cases where it is an awesome feature to
have.

I do not know how to set the NOWAIT flag in C++. In Delphi, you double
click the transaction component and select the No Wait radio button ;)
Perhaps someone else can shed some light or you can ask in the ODBC group.

Adam