Re: High rate of transaction failure with the Serializable Isolation Level
От | Craig Ringer |
---|---|
Тема | Re: High rate of transaction failure with the Serializable Isolation Level |
Дата | |
Msg-id | 53D0923B.7050501@2ndquadrant.com обсуждение исходный текст |
Ответ на | High rate of transaction failure with the Serializable Isolation Level (Reza Taheri <rtaheri@vmware.com>) |
Ответы |
Re: High rate of transaction failure with the
Serializable Isolation Level
(Reza Taheri <rtaheri@vmware.com>)
|
Список | 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
В списке pgsql-performance по дате отправления:
Следующее
От: Craig RingerДата:
Сообщение: Re: High rate of transaction failure with the Serializable Isolation Level