Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1odjUu-0004mG-JR for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Sep 2022 02:42:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1odjUs-0003zY-J2 for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Sep 2022 02:41:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1odjUs-0003zI-9U for pgsql-bugs@lists.postgresql.org; Thu, 29 Sep 2022 02:41:58 +0000 Received: from mail-pf1-x444.google.com ([2607:f8b0:4864:20::444]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1odjUp-0005tY-P8 for pgsql-bugs@lists.postgresql.org; Thu, 29 Sep 2022 02:41:57 +0000 Received: by mail-pf1-x444.google.com with SMTP id a29so233600pfk.5 for ; Wed, 28 Sep 2022 19:41:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date; bh=k/944TNO0i03D6OGZhiuVpTVUtKg0tYpTLwIWwg3Ioo=; b=JbeMerbbQnqgOOTEq6CkbBh9Pe/FUtkNwucwMT+ER/pZGQI9ASRIDXBjC/gCKgvbJ2 0nCSmnunJ45nyGyNEnr+tSuO6Vkl0gqtyQs0o8Lr0s+UKs+1aJbDDDUlqkxgM+1xtiMw 8eVREv8Q4HL1tNbgvmXhM64ldK5F8p4q0G9/cpCOXkGfM6kLNx2U4LvDAJT8FsbuMg+I jqV3j8hCB0C7l2w8IwHighgykkADgGhxiANu1Kiv9xtvp1hopEgSu3Cn77u4RI3yiidn r3NQ+FqXgEZdR+Q/D4IGDyO76FO4UW+sF15n9hsrCC432vPADyodqnuxX/MSToYKs6ez DvMA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date; bh=k/944TNO0i03D6OGZhiuVpTVUtKg0tYpTLwIWwg3Ioo=; b=py9KGJNfd55DqPZNVSy8i4zzJpFAmLhy67qziUSH+/s9GH+Qd9glHzQj4R5zO7ZkeJ yMNpdVdObqcmP+Rv7ZzaHJth+u82lSYZ84QnjJHodfqPgaRnJBjnbZAfXyADquCieYnQ nKurRaLJAixEwCmcQGRG+uWe2R6qZNnrC4EBDuQ+eQoAyfIS4KffIHzY7pPf0DQJehKn qtKnfgHJQC+AXprHFvyPCVewQT/U918G5e5yuZNOUMBB8q/ksxYI3KuuJFFxk0O+dra0 kLm9GXwnOGaS6MgqJocQGRF68daY/vzAzIThRqBhpLAIjhClHaXVoukRJIRredh28okt MEpQ== X-Gm-Message-State: ACrzQf1NTLEe4cdN5eOch2Jr1jKm4fGBVfpOPNiOzcQD3cOSO67cOCkR XIhV5wiUVvLJbpWfkKu1AWPdvK9FH9w= X-Google-Smtp-Source: AMsMyM4rAUWkiRgfOz6IZGLGZSNm1PJITbq5krLWafHYAxVDckFvZwysdL+YZdYDEF0ppekcEX/hzA== X-Received: by 2002:a63:5a05:0:b0:434:23a5:a5ca with SMTP id o5-20020a635a05000000b0043423a5a5camr855227pgb.515.1664419313060; Wed, 28 Sep 2022 19:41:53 -0700 (PDT) Received: from [198.18.35.137] ([199.168.112.175]) by smtp.gmail.com with ESMTPSA id h28-20020a056a00001c00b00540ffb28da0sm4827154pfk.91.2022.09.28.19.41.51 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 28 Sep 2022 19:41:52 -0700 (PDT) Date: Thu, 29 Sep 2022 10:41:42 +0800 From: Zhang Mingli To: pgsql-bugs@lists.postgresql.org, Glen Mailer Message-ID: <33029e69-53fd-44b7-81be-4bd3891cf23b@Spark> In-Reply-To: References: Subject: Re: Possible bug with SKIP LOCKED behaviour X-Readdle-Message-ID: 33029e69-53fd-44b7-81be-4bd3891cf23b@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="633505ec_440badfc_b051" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --633505ec_440badfc_b051 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, On Sep 29, 2022, 00:56 +0800, Glen Mailer , wrote:= > Hello everyone > > I believe I've run into a bug in the behaviour of SKIP LOCKED, where I = have a program that implements a queue with concurrent workers SELECTing = work from some shared tables. > > The code in question does a LE=46T JOIN across two tables with a =46OR = UPDATE on the left table and a SKIP LOCKED clause, and then UPDATEs or IN= SERTs rows into the table on right side of the JOIN in a way that leads t= o subsequent executions of the same query to no longer match those rows. = However, when run concurrently I'm seeing the same row be selected by mul= tiple workers - which shouldn't be possible based on my understanding of = the relevant semantics of these operations. Perhaps I'm just holding it w= rong, but I would have expected the =46OR UPDATE lock on the left table t= o be sufficient to avoid overlapping results. > > I have extracted a fairly minimal reproducing case from our production = code, which includes some Go code as a test harness to run the queries co= ncurrently enough to demonstrate the problem - this can be found at=C2=A0= https://github.com/glenjamin/postgres-skip-locked-surprise > I wasn't sure how much detail from that reproducing case to repeat in t= his email, so I've only gone with an outline of the observed and expected= behaviour - but I can try and add more detail to this thread if desired > > Cheers > Glen According to doc: With=C2=A0SKIP LOCKED, any selected rows that cannot be immediately locke= d are skipped. Skipping locked rows provides an inconsistent view of the = data, so this is not suitable for general purpose work, but can be used t= o avoid lock contention with multiple consumers accessing a queue-like ta= ble. > this can be found at=C2=A0https://github.com/glenjamin/postgres-skip-lo= cked-surprise And a golang script is not convenient for hackers to reproduce. Could you= provide some steps to produce the bug stably if it really was =3F Regards, Zhang Mingli --633505ec_440badfc_b051 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi,

On Sep 29, 2022, 00:56 +0800, Glen Mailer <glen=40geckoboard.com>, = wrote:
Hello everyone

I believe I've run into a bug in the behaviour of SKIP LOCKED, where I ha= ve a program that implements a queue with concurrent workers SELECTing wo= rk from some shared tables.

The code in question does a LE=46T JOIN across two tables with a =46OR UP= DATE on the left table and a SKIP LOCKED clause, and then UPDATEs or INSE= RTs rows into the table on right side of the JOIN in a way that leads to = subsequent executions of the same query to no longer match those rows. Ho= wever, when run concurrently I'm seeing the same row be selected by multi= ple workers - which shouldn't be possible based on my understanding of th= e relevant semantics of these operations. Perhaps I'm just holding it wro= ng, but I would have expected the =46OR UPDATE lock on the left table to = be sufficient to avoid overlapping results.

I have extracted a fairly minimal reproducing case from our production co= de, which includes some Go code as a test harness to run the queries conc= urrently enough to demonstrate the problem - this can be found at&=23160;= https://github.com/glenjamin/postgres-skip-locke= d-surprise
I wasn't sure how much detail from that reproducing case to repeat in thi= s email, so I've only gone with an outline of the observed and expected b= ehaviour - but I can try and add more detail to this thread if desired
Cheers
Glen
According to doc:&=23160;

With&=23160;SKIP LOCKED, = any selected rows that cannot be immediately locked are skipped. Skipping= locked rows provides an inconsistent view of the data, so this is not su= itable for general purpose work, but can be used to avoid lock contention= with multiple consumers accessing a queue-like table.
this can be found at&=23160;https://github.com= /glenjamin/postgres-skip-locked-surprise

And a golang script is not convenient for hackers t= o reproduce. Could you provide some steps to produce the bug stably if it= really was =3F&=23160;


Regards,
Zhang Mingli

--633505ec_440badfc_b051--