[Info-ingres] Deadlock on a sequence

[Alex Hanshaw]

Ah, I think originally my create sequence was uncommitted.
Taking a look at the lock_trace for the select of nextval I'm not sure I see an opportunity for deadlock unless it related to inserting the nextval into a table.


From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> On Behalf Of Roy Hann
Sent: 06 March 2018 16:17
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Deadlock on a sequence

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

_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org<mailto:Info-ingres at lists.planetingres.org>
http://lists.planetingres.org/mailman/listinfo/info-ingres<http://lists.planetingres.org/mailman/listinfo/info-ingres>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180306/e32592a0/attachment.html>