[Info-ingres] Deadlock on a sequence

[Roy Hann]

Alex Hanshaw wrote:

> You can certainly deadlock on a sequence and a table across [two]
> sessions accessing them in the reverse order. Is that what you meant?

Not exactly. I was wondering if the SELECT myseq.nextval could
ever provoke a deadlock.

I wouldn't expect it could but I was shown an error log that suggested
it might. I couldn't devise a test to demonstrate it though.

> S1:
> create sequence myseq \g
> create table test as select reltid from iirelation \p\g
> commit; \g
> update test set reltid = reltid + 1 \g
>
> S2:
> SELECT myseq.nextval AS mynextval; \g
>
> S1:
> SELECT myseq.nextval AS mynextval;\g
>
> S2:
> update test set reltid = reltid + 1 \g
>
> S2 drops out with deadlock. 

For me S2 just goes into a lockwait, as I'd have expected. 

Roy