Re: High rate of transaction failure with the Serializable Isolation Level - Mailing list pgsql-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?

From the documentation
(http://www.postgresql.org/docs/current/static/transaction-iso.html):

> Predicate locks in PostgreSQL, like in most other database systems, are based on data actually accessed by a
transaction.These will show up in the pg_locks system view with a mode of SIReadLock. The particular locks acquired
duringexecution of a query will depend on the plan used by the query, and multiple finer-grained locks (e.g., tuple
locks)may be combined into fewer coarser-grained locks (e.g., page locks) during the course of the transaction to
preventexhaustion of the memory used to track the locks. 

... so yes, it may raise locks to page level. That doesn't mean that's
necessarily what's happening here.

> Are there any knobs I can play with to alleviate
> this?

A lower FILLFACTOR can spread data out at the cost of wasted space.

> FWIW, the same transactions on MS SQL Server see almost no conflicts.

Many DBMSs don't detect all serialization anomalies. PostgreSQL doesn't
detect all possible anomalies but it detects many that other systems may
not.

To see what's going on and why MS SQL Server (version?) doesn't
complain, it'd be best to boil each case down to a minimal reproducible
test case that can be analyzed in isolation.

PostgreSQL's isolationtester tool, in src/test/isolation, can be handy
for automating this kind of conflict, and provides some useful examples
of cases that are detected.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services