Advanced
Mailing List Archive

Mailing List Archive

  1. Home >
  2. Request Tracker>
  3. Users
Postgresql default isolation level (was Re: mysql DB engine ndbdcluster)
vaclav.ovsik at i
Dec 21, 2016, 9:38 AM
Post #1 of 2 (752 views)
Permalink
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@rt ERROR: deadlock detected
2016-12-21 11:33:38 CET [22545-2] rt_rt@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@rt HINT: See server log for query details.
2016-12-21 11:33:38 CET [22545-4] rt_rt@rt CONTEXT: while updating tuple (4336,144) in relation "tickets"
2016-12-21 11:33:38 CET [22545-5] rt_rt@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@rt ERROR: could not serialize access due to concurrent update
2016-12-21 17:20:41 CET [25923-2] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1 FOR UPDATE
2016-12-21 17:20:41 CET [25923-3] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-4] rt_rt@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@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-6] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-21 17:20:41 CET [25923-7] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-8] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-21 17:20:41 CET [25923-9] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-10] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-21 17:20:41 CET [25923-11] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-12] rt_rt@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@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-14] rt_rt@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@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-16] rt_rt@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@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-18] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-21 17:20:41 CET [25923-19] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-20] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-21 17:20:41 CET [25923-21] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-22] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-21 17:20:41 CET [25923-23] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-24] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-21 17:20:41 CET [25923-25] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-26] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-21 17:20:41 CET [25923-27] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-28] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-21 17:20:41 CET [25923-29] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-30] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-21 17:20:41 CET [25923-31] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-32] rt_rt@rt STATEMENT: SELECT * FROM Users WHERE id = $1
2016-12-21 17:20:41 CET [25923-33] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-34] rt_rt@rt STATEMENT: SELECT * FROM Users WHERE id = $1
2016-12-21 17:20:41 CET [25923-35] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-21 17:20:41 CET [25923-36] rt_rt@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
Re: Postgresql default isolation level (was Re: mysql DB engine ndbdcluster) [ In reply to ]
vaclav.ovsik at i
Dec 22, 2016, 5:03 AM
Post #2 of 2 (743 views)
Permalink
On Wed, Dec 21, 2016 at 06:38:08PM +0100, Václav Ovsík wrote:
>...
> ================================================================
> * default_transaction_isolation = 'serializable'
> - I tried the action many times, but Pg is silent - nothing appears
> in its log file and everything seems normal.

Sorry, this is not true. I did more thorough testing today. I did experiments
on one test ticket and as the history of ticket grows, the probability of the
bug increases. Now it is almost certain the problem will occurs.
Isolation level 'serializable' behaves like 'repeatable read'. So the summary is:

===========================================================
'commited read': -> deadlock, application outputs error:

Comments added
Could not change owner: Could not update column Owner: Owner could not be set to 102.

Postgres log:

2016-12-22 13:18:18 CET [26070-1] rt_rt@rt ERROR: deadlock detected
2016-12-22 13:18:18 CET [26070-2] rt_rt@rt DETAIL: Process 26070 waits for ShareLock on transaction 32889; blocked by process 26097.
Process 26097 waits for ShareLock on transaction 32890; blocked by process 26070.
Process 26070: UPDATE Tickets SET Owner=$1 WHERE id=$2
Process 26097: INSERT INTO GroupMembers (LastUpdatedBy, Creator, Created, GroupId, MemberId, LastUpdated) VALUES ($1, $2, $3, $4, $5, $6)
2016-12-22 13:18:18 CET [26070-3] rt_rt@rt HINT: See server log for query details.
2016-12-22 13:18:18 CET [26070-4] rt_rt@rt CONTEXT: while updating tuple (4509,284) in relation "tickets"
2016-12-22 13:18:18 CET [26070-5] rt_rt@rt STATEMENT: UPDATE Tickets SET Owner=$1 WHERE id=$2



===========================================================
'repeatable read'
'serializable': -> application output normal status:

Comments added
Owner changed from eva to zito

Postgres log:
2016-12-22 13:26:36 CET [31696-1] rt_rt@rt ERROR: could not serialize access due to concurrent update
2016-12-22 13:26:36 CET [31696-2] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1 FOR UPDATE
2016-12-22 13:26:36 CET [31696-3] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-4] rt_rt@rt STATEMENT: INSERT INTO Transactions (Type, Creator, ObjectId, NewValue, Field, Data, ObjectType, NewReference, ReferenceType, Created, OldReference, OldValue) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
2016-12-22 13:26:36 CET [31696-5] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-6] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-22 13:26:36 CET [31696-7] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-8] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-22 13:26:36 CET [31696-9] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-10] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-22 13:26:36 CET [31696-11] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-12] rt_rt@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-22 13:26:36 CET [31696-13] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-14] rt_rt@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-22 13:26:36 CET [31696-15] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-16] rt_rt@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-22 13:26:36 CET [31696-17] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-18] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-22 13:26:36 CET [31696-19] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-20] rt_rt@rt STATEMENT: SELECT * FROM Transactions WHERE id = $1
2016-12-22 13:26:36 CET [31696-21] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-22] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-22 13:26:36 CET [31696-23] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-24] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-22 13:26:36 CET [31696-25] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-26] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-22 13:26:36 CET [31696-27] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-28] rt_rt@rt STATEMENT: UPDATE Tickets SET LastUpdated=$1 WHERE id=$2
2016-12-22 13:26:36 CET [31696-29] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-30] rt_rt@rt STATEMENT: SELECT * FROM Tickets WHERE id = $1
2016-12-22 13:26:36 CET [31696-31] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-32] rt_rt@rt STATEMENT: SELECT * FROM Users WHERE id = $1
2016-12-22 13:26:36 CET [31696-33] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-34] rt_rt@rt STATEMENT: SELECT * FROM Users WHERE id = $1
2016-12-22 13:26:36 CET [31696-35] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-36] rt_rt@rt STATEMENT: SELECT * FROM Principals WHERE id = $1
2016-12-22 13:26:36 CET [31696-37] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-38] rt_rt@rt STATEMENT: SELECT main.* FROM Attributes main WHERE (main.ObjectId = 1900) AND (main.ObjectType = 'RT::User') ORDER BY main.id ASC
2016-12-22 13:26:36 CET [31696-39] rt_rt@rt ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-12-22 13:26:36 CET [31696-40] rt_rt@rt STATEMENT: SELECT main.* FROM Attributes main WHERE (main.ObjectId = 1900) AND (main.ObjectType = 'RT::User') ORDER BY main.id ASC


It seems to me, that a transaction - comment and owner change is processed
by one RT server process and concurrently runs another web server
process gathering data for ticket history. I have a rather long ticket
history now. I added many comments already. The probability of
transaction collision is very high, so the problem occurs every time.

Are there any changes in RT between 4.2 and 4.4 toward loading web page
asynchronously? Is it possible data are loading from browser while
a modify transaction is in progress yet.

--
Zito

©2024 GT.net. A Gossamer Threads company.
5th Floor, 455 Granville St., Vancouver, BC V6C 1T1, Canada | Legal