From: Craig Ringer <craig@2ndquadrant.com>
To: Reza Taheri <rtaheri@vmware.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the Serializable Isolation Level
Date: Thu, 24 Jul 2014 12:57:31 +0800
Message-ID: <53D0923B.7050501@2ndquadrant.com> (raw)
In-Reply-To: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 07/24/2014 09:18 AM, Reza Taheri wrote:
> What is unusual is that the majority of the failures occur in a
> statement that should not have any isolation conflicts. About 17K of
> failures are from the statement below:
It's not just that statement that is relevant.
At SERIALIZABLE isolation the entire transaction's actions must be
considered, as must the conflicting transaction.
> This doesn’t make sense since at any given time, only one transaction
> might possibly be accessing the row that is being updated. There should
> be no conflicts if we have row-level locking/isolation.
Is that statement run standalone, or as part of a larger transaction?
> The second most common conflict happens 7.6K times in the statement below:
...
> I don’t understand why an insert would hit a serialization conflict
If the INSERTing transaction previously queried for a key that was
created by a concurrent transaction this can occur as there is no
serialization execution order of the transactions that could produce the
same result.
This doesn't produce exactly the same error, but demonstrates one such case:
regress=> CREATE TABLE demo (id integer primary key, value integer);
CREATE TABLE
regress=> INSERT INTO demo(id, value) VALUES (1, 42);
INSERT 0 1
then
regress=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
regress=> SELECT id FROM demo WHERE id = 2;
id
----
(0 rows)
session1=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
session2=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
session1=> SELECT id FROM demo WHERE id = 2;
id
----
(0 rows)
session2=> SELECT id FROM demo WHERE id = 3;
id
----
(0 rows)
session1=> INSERT INTO demo VALUES (3, 43);
INSERT 0 1
session2=> INSERT INTO demo VALUES (2, 43);
INSERT 0 1
session2=> COMMIT;
COMMIT
session1=> COMMIT;
ERROR: could not serialize access due to read/write dependencies among
transactions
DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT: The transaction might succeed if retried.
> Does PGSQL raise locks to page level when we run with
> SQL_TXN_SERIALIZABLE?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@2ndquadrant.com, rtaheri@vmware.com
Subject: Re: High rate of transaction failure with the Serializable Isolation Level
In-Reply-To: <53D0923B.7050501@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox