Thread: Set transaction waiting mode and timeout

Hi!

In Firebird the transaction can be parameterizable as:

SET TRANSACTION
   ...
   [WAIT | NO WAIT]
   [LOCK TIMEOUT seconds]

The "NO WAIT" is force transaction to return instantly with errormsg
when some deadlock situation happened, Timeout is define the seconds
for wait.
This can be set for each transaction I used.

I search for same option in pg if exists.
Because when I do this (theid field is primary key):

Con1.Begin
Con2.Begin
Con1.Exec(insert into x (theid, value) values (1, 'a'));
Con2.Exec(insert into x (theid, value) values (1, 'a'));
...
then Con2 is remaining in deadlock state (nothing happened, the code
is waiting for the transaction's end).

When Con1's transaction is end, Con2 is continue...

But users better like the ErrorMessage than incomprehensible "freezed screen".
And I also better like the controllable situation (I can re-try 3 times, etc.).

Thanks for your help:
     dd

On Wed, May 18, 2011 at 3:01 AM, Durumdara <durumdara@gmail.com> wrote:
> Hi!
>
> In Firebird the transaction can be parameterizable as:
>
> SET TRANSACTION
>   ...
>   [WAIT | NO WAIT]
>   [LOCK TIMEOUT seconds]
>
> The "NO WAIT" is force transaction to return instantly with errormsg
> when some deadlock situation happened, Timeout is define the seconds
> for wait.
> This can be set for each transaction I used.
>
> I search for same option in pg if exists.
> Because when I do this (theid field is primary key):
>
> Con1.Begin
> Con2.Begin
> Con1.Exec(insert into x (theid, value) values (1, 'a'));
> Con2.Exec(insert into x (theid, value) values (1, 'a'));
> ...
> then Con2 is remaining in deadlock state (nothing happened, the code
> is waiting for the transaction's end).

That is not a deadlock state.  A deadlock state is where con2 is
waiting on con1 AND con1 is waiting on con2.

> When Con1's transaction is end, Con2 is continue...

Then it's not deadlocked.  It's just locked.

> But users better like the ErrorMessage than incomprehensible "freezed screen".
> And I also better like the controllable situation (I can re-try 3 times, etc.).

You can do a select ... for update nowait, that might get you what you want.