[rt-users] Postgresql default isolation level (was Re: mysql DB engine ndbdcluster)

Václav Ovsík vaclav.ovsik at i.cz
Wed Dec 21 12:38:08 EST 2016


Hi,

On Thu, Dec 15, 2016 at 12:07:54AM -0800, Alex Vandiver wrote:
>...
> However, upon writing this, it occurs to me that Postgres' default
> isolation level is _also_ "read committed."[4]  Thus any possible race
> conditions that might show up under NDB are also possible under
> Postgres.  I'd need to do some close analysis to determine if this
> means that Postgres is open to data corruption, or if both are safe
> because the queries RT runs do not care about repeatable-read
> semantics.
>...

I afraid you are right, Postgreses default isolation level is 'read
committed'. I filled bug-report two months ago.
https://issues.bestpractical.com/Ticket/Display.html?id=32381

I'm running PostgreSQL & RT on Debian Jessie, that is Pg 9.4 & RT 4.4.1.
I did only a basic PostgreSQL performance tuning, but didn't change the
default transaction isolation level. I can't find anything about needed
transaction isolation level through RT documentation. I read this fact,
that RT assumes "repeatable-read" isolation level for the first time.

Nevertheless we are hit only by the problem during Comment/Correspond
together with ticket owner change only (I hope).

I did some testing right now on testing RT instance:

 ================================================================
 * default "committed read" isolation level
   - I'm getting from time to time:

    2016-12-21 11:33:38 CET [22545-1] rt_rt at rt ERROR:  deadlock detected
    2016-12-21 11:33:38 CET [22545-2] rt_rt at rt DETAIL:  Process 22545 waits for ShareLock on transaction 8351856; blocked by process 22539.
	    Process 22539 waits for ShareLock on transaction 8351857; blocked by process 22545.
	    Process 22545: UPDATE Tickets SET Owner=$1 WHERE id=$2 
	    Process 22539: INSERT INTO GroupMembers (LastUpdatedBy, LastUpdated, GroupId, MemberId, Creator, Created) VALUES ($1, $2, $3, $4, $5, $6)
    2016-12-21 11:33:38 CET [22545-3] rt_rt at rt HINT:  See server log for query details.
    2016-12-21 11:33:38 CET [22545-4] rt_rt at rt CONTEXT:  while updating tuple (4336,144) in relation "tickets"
    2016-12-21 11:33:38 CET [22545-5] rt_rt at rt STATEMENT:  UPDATE Tickets SET Owner=$1 WHERE id=$2 

 ================================================================
 * default_transaction_isolation = 'repeatable read'
   - I'm getting the following errors, but on the application level
     things seems to be normal.

    2016-12-21 17:20:41 CET [25923-1] rt_rt at rt ERROR:  could not serialize access due to concurrent update
    2016-12-21 17:20:41 CET [25923-2] rt_rt at rt STATEMENT:  SELECT * FROM Tickets WHERE id = $1 FOR UPDATE
    2016-12-21 17:20:41 CET [25923-3] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-4] rt_rt at rt STATEMENT:  INSERT INTO Transactions (OldReference, ObjectType, Data, Field, ObjectId, Type, NewValue, ReferenceType, OldValue, Created, NewReference, Creator) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
    2016-12-21 17:20:41 CET [25923-5] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-6] rt_rt at rt STATEMENT:  SELECT  * FROM Transactions WHERE id = $1
    2016-12-21 17:20:41 CET [25923-7] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-8] rt_rt at rt STATEMENT:  SELECT  * FROM Tickets WHERE id = $1
    2016-12-21 17:20:41 CET [25923-9] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-10] rt_rt at rt STATEMENT:  SELECT  * FROM Transactions WHERE id = $1
    2016-12-21 17:20:41 CET [25923-11] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-12] rt_rt at rt STATEMENT:  SELECT main.* FROM Scrips main JOIN ObjectScrips ObjectScrips_1  ON ( ObjectScrips_1.Scrip = main.id ) JOIN ScripConditions ScripConditions_2  ON ( ScripConditions_2.id = main.ScripCondition )  WHERE (ObjectScrips_1.ObjectId = '0') AND (ObjectScrips_1.Stage = 'TransactionCreate') AND (ScripConditions_2.ApplicableTransTypes LIKE '%Comment%' OR ScripConditions_2.ApplicableTransTypes LIKE '%Any%') AND (main.Disabled = '0')   GROUP BY main.id   ORDER BY MIN(ObjectScrips_1.SortOrder) ASC 
    2016-12-21 17:20:41 CET [25923-13] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-14] rt_rt at rt STATEMENT:  SELECT COUNT(DISTINCT main.id) FROM Scrips main JOIN ObjectScrips ObjectScrips_1  ON ( ObjectScrips_1.Scrip = main.id ) JOIN ScripConditions ScripConditions_2  ON ( ScripConditions_2.id = main.ScripCondition )  WHERE (ObjectScrips_1.ObjectId = '0') AND (ObjectScrips_1.Stage = 'TransactionCreate') AND (ScripConditions_2.ApplicableTransTypes LIKE '%Comment%' OR ScripConditions_2.ApplicableTransTypes LIKE '%Any%') AND (main.Disabled = '0') 
    2016-12-21 17:20:41 CET [25923-15] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-16] rt_rt at rt STATEMENT:  SELECT main.* FROM Scrips main JOIN ObjectScrips ObjectScrips_1  ON ( ObjectScrips_1.Scrip = main.id ) JOIN ScripConditions ScripConditions_2  ON ( ScripConditions_2.id = main.ScripCondition )  WHERE (ObjectScrips_1.ObjectId = '0') AND (ObjectScrips_1.Stage = 'TransactionCreate') AND (ScripConditions_2.ApplicableTransTypes LIKE '%Comment%' OR ScripConditions_2.ApplicableTransTypes LIKE '%Any%') AND (main.Disabled = '0')   GROUP BY main.id   ORDER BY MIN(ObjectScrips_1.SortOrder) ASC 
    2016-12-21 17:20:41 CET [25923-17] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-18] rt_rt at rt STATEMENT:  SELECT  * FROM Tickets WHERE id = $1
    2016-12-21 17:20:41 CET [25923-19] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-20] rt_rt at rt STATEMENT:  SELECT  * FROM Transactions WHERE id = $1
    2016-12-21 17:20:41 CET [25923-21] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-22] rt_rt at rt STATEMENT:  SELECT  * FROM Tickets WHERE id = $1
    2016-12-21 17:20:41 CET [25923-23] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-24] rt_rt at rt STATEMENT:  UPDATE Tickets SET LastUpdated=$1 WHERE id=$2 
    2016-12-21 17:20:41 CET [25923-25] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-26] rt_rt at rt STATEMENT:  UPDATE Tickets SET LastUpdated=$1 WHERE id=$2 
    2016-12-21 17:20:41 CET [25923-27] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-28] rt_rt at rt STATEMENT:  UPDATE Tickets SET LastUpdated=$1 WHERE id=$2 
    2016-12-21 17:20:41 CET [25923-29] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-30] rt_rt at rt STATEMENT:  SELECT  * FROM Tickets WHERE id = $1
    2016-12-21 17:20:41 CET [25923-31] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-32] rt_rt at rt STATEMENT:  SELECT  * FROM Users WHERE id = $1
    2016-12-21 17:20:41 CET [25923-33] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-34] rt_rt at rt STATEMENT:  SELECT  * FROM Users WHERE id = $1
    2016-12-21 17:20:41 CET [25923-35] rt_rt at rt ERROR:  current transaction is aborted, commands ignored until end of transaction block
    2016-12-21 17:20:41 CET [25923-36] rt_rt at rt STATEMENT:  SELECT  * FROM Users WHERE id = $1

 ================================================================
 * default_transaction_isolation = 'serializable'
   - I tried the action many times, but Pg is silent - nothing appears
     in its log file and everything seems normal.


Is there somebody with RT 4.4.1 (versions 4.2.x not suffers by this)
and PostgreSQL with similar symptoms?

The problem occurs probably only when we did action Comment or
Correspond and change the ticket owner in the one transaction.

Is it safe for me to change the default_transaction_isolation 
in /etc/postgresql/9.4/main/postgresql.conf to 'repeatable read' ?
I'm a bit confused by errors above, maybe the failed transaction was
restarted?

Regards
-- 
Zito



More information about the rt-users mailing list