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 1pVIz9-0002Z9-6i for pgsql-sql@arkaria.postgresql.org; Thu, 23 Feb 2023 21:18:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pVIz8-0006Yh-1z for pgsql-sql@arkaria.postgresql.org; Thu, 23 Feb 2023 21:18:38 +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 1pVIz7-0006YY-N6 for pgsql-sql@lists.postgresql.org; Thu, 23 Feb 2023 21:18:37 +0000 Received: from mail-pg1-x529.google.com ([2607:f8b0:4864:20::529]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pVIz3-0006O2-44 for pgsql-sql@lists.postgresql.org; Thu, 23 Feb 2023 21:18:37 +0000 Received: by mail-pg1-x529.google.com with SMTP id h31so6431238pgl.6 for ; Thu, 23 Feb 2023 13:18:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=20210112; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=YX/BYgrqBmEZh4KqupNuqYHw2Er8LB3CrE2YVdfQRCQ=; b=AsU5FA0uKkDlqJ8B2VAAIStYfzLmO02MP9+ZVPosK+7TmJrF44k2f1cmykZFV7ttMS ldwUhtb3nSfBTOSexPX6BWFaOYGk0vDlTl3I5a61As8AYCgJxEmgJ60nq4qZhGBV66xD zAOBhWGRrhxDHl9/opzddBleg95RmbjBxSpHOINX9Aw4AzV4CPWSPa58eGszb8DQzsa6 FJ7qMm9gYhDWzsE6SInYcYe9bWeB/wmo58o4icpR+REUX/2/LtgXYB//7ZJcpXdAvs1M dArWmVeMxZgpC1wFajRzFkNljZKzDy99W1L2K8MfFKC4Ml9iEFmwwlk9hRQYVtUizzQh wyYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=YX/BYgrqBmEZh4KqupNuqYHw2Er8LB3CrE2YVdfQRCQ=; b=jstMTpsw2LRgx2cYs+YIpJySSFPpeeSzr4zVes+In0yDxeLQ6fbC3Im1HbP2Cfwmnq NPmzqGAPlcKlse9r4E/8b0M0rWcT9WbvLeqRFJ7E/ZbYXgwT0aOWSGME1/Ed6DZCJ5Im R631/XLaTR+INcsKBQzAoUd7ue3K0EhDTsIuZcyPtIrjhWdpmaFOWbQ5k6pFNIUJUZLr 665/BtEvjo7AP0jc+UbxRxB5VZCkAdNSz1j1yR+upjmLLtU4ZjwNH+ivcyYTiaNgeY/x vqNQpHy9B/03WFF1NTuVX8xOwQukTDGzIoE+uJhynnTmQn0oa5Ap+aeAkfTSKt8U0Xan /ZtQ== X-Gm-Message-State: AO0yUKU7oScHWM+l4gfdJWoknWZeDKIB6zaMG2fSib0+X0CyL79V7/sK 2jlfA22hH1NDxPLK8sXf8+1Mpw1660h42aMIOhDE9IuWfW4= X-Google-Smtp-Source: AK7set/tbUjnDK6OkSym8WpbHd4L3/M3E9SwoicTR2yng53HC27W8rfBocX2cE3NnIUJhRBq8cd7kr6XHcQM+A/N2Ao= X-Received: by 2002:a63:3c4e:0:b0:4fb:1f2a:e6c6 with SMTP id i14-20020a633c4e000000b004fb1f2ae6c6mr2016105pgn.2.1677187110455; Thu, 23 Feb 2023 13:18:30 -0800 (PST) MIME-Version: 1.0 From: michele curioni Date: Thu, 23 Feb 2023 21:18:20 +0000 Message-ID: Subject: Could not serialize access due to read/write dependencies among transactions To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f0cead05f5648df7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f0cead05f5648df7 Content-Type: text/plain; charset="UTF-8" Hi, we have a Spring Boot application that tries to update two separate rows in a table in 2 separate threads, and it uses the default Transaction Isolation level of READ_COMMITTED. One of the two threads always succeeds, the other always fails with: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions [java] ERROR> Detail: Reason code: Canceled on identification as a pivot, during write. [java] ERROR> Hint: The transaction might succeed if retried. I thought that error could only happen for SERIALIZABLE transactions, and moreover, the two threads are trying to update different rows. The sql update has a where clause on the primary key: update table set status='a' where column_a = {value1} and column_b={value2} The table's primary key is a composition of column_a and column_b thread1 and thread2 use different values for value1 and value2 Does anyone have any idea how the problem could happen? Thanks, Michele --000000000000f0cead05f5648df7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
we have a Spring Boot application that tries to up= date two separate rows in a table in 2 separate threads, and it uses the de= fault Transaction Isolation level of READ_COMMITTED.
One of the t= wo threads always succeeds, the other always fails with:
org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
 [java] ERROR>  Detail: Reason =
code: Canceled on identification as a pivot, during write.
 [java] ERROR>  Hint: The trans=
action might succeed if retried.

I th= ought that error could only happen for SERIALIZABLE transactions, and moreo= ver, the two threads are trying to update different rows.

The sql update has a where clause on the primary key:
upd= ate table set status=3D'a' where column_a =3D {value1} and column_b= =3D{value2}

The table's primary key is a compo= sition of column_a and column_b

=C2=A0thread1 and = thread2 use different values for value1 and value2

Does anyone have any idea how the problem could happen?

Thanks,
Michele=C2=A0
--000000000000f0cead05f5648df7--