Message-ID: <53D0923B.7050501@2ndquadrant.com>
Date: Thu, 24 Jul 2014 12:57:31 +0800
From: Craig Ringer <craig@2ndquadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Reza Taheri <rtaheri@vmware.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the Serializable
 Isolation Level
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
In-Reply-To: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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?