pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
postgresql : could not serialize access due to read/write dependencies among transactions 4+ messages / 4 participants [nested] [flat]
* postgresql : could not serialize access due to read/write dependencies among transactions @ 2017-01-18 04:54 Neslisah Demirci <neslisah.demirci@markafoni.com> 0 siblings, 3 replies; 4+ messages in thread From: Neslisah Demirci @ 2017-01-18 04:54 UTC (permalink / raw) To: pgsql-general <pgsql-general@postgresql.org>; pgsql-admin Hi , I have problem about this issue ; could not serialize access due to read/write dependencies among transactions A message app like whatsapp i decided to use isolation level serializable if i use other transaction levels many conversations started with same number (think about whatsapp web sidebar multipling with same number every message). I also add an index to my query and my query's execution plan don't use seq scan . How can i solve this issue ? Can i solve this on db orr on app ? Nesliþah Demirci | Veritabaný Yöneticisi Ayazaða cad. No:4 Uniq Ýstanbul Plaza B2 /Kat:3 34396 Ayazaða-SARIYER-Ýstanbul T. (+90) 212 453 16 00 - 5516 F. (+90) 212 453 16 16 www.markafoni.com<http://www.markafoni.com/; www.facebook.com/markafoni<http://www.facebook.com/markafoni; blog.markafoni.com ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql : could not serialize access due to read/write dependencies among transactions @ 2017-01-18 05:10 John R Pierce <pierce@hogranch.com> parent: Neslisah Demirci <neslisah.demirci@markafoni.com> 2 siblings, 0 replies; 4+ messages in thread From: John R Pierce @ 2017-01-18 05:10 UTC (permalink / raw) To: pgsql-general@postgresql.org On 1/17/2017 8:54 PM, Neslisah Demirci wrote: > > > could not serialize access due to read/write dependencies among > transactions > only way to answer this would be to know what the various concurrent transactions are doing. > A message app like whatsapp i decided to use isolation level > serializable if i use other transaction levels many conversations > started with same number (think about whatsapp web sidebar multipling > with same number every message). again, we'd need to know what your transactions are doing. what you're describing sounds inherently incorrect, like you're doing too much in the application and not enough in the database > I also add an index to my query and my query's execution plan don't > use seq scan . > that has no bearing on the above problems. -- john r pierce, recycling bits in santa cruz ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql : could not serialize access due to read/write dependencies among transactions @ 2017-01-18 17:10 Kevin Grittner <kgrittn@gmail.com> parent: Neslisah Demirci <neslisah.demirci@markafoni.com> 2 siblings, 0 replies; 4+ messages in thread From: Kevin Grittner @ 2017-01-18 17:10 UTC (permalink / raw) To: Neslisah Demirci <neslisah.demirci@markafoni.com>; +Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-admin On Tue, Jan 17, 2017 at 10:54 PM, Neslisah Demirci <neslisah.demirci@markafoni.com> wrote: > could not serialize access due to read/write dependencies among > transactions > I also add an index to my query and my query's execution plan > don't use seq scan . These two issues are likely to be somewhat related -- if a sequential scan is used, then any write to that table by another connection causes a read-write dependency (a/k/a rw-conflict), which can eventually contribute to a serialization failure. If you can cause narrower access through indexes, you may see a significant drop in the frequency of these serialization failures. You might want to post the query and its execution plan with all the information suggested here (exact pg version, configuration information, machine descriptions, etc.): https://wiki.postgresql.org/wiki/SlowQueryQuestions On the other hand, if you are going to use serializable transactions (or even repeatable read transactions) you should probably be using some framework that can retry the transaction from the start on a serialization failure. You might be interested in this set of examples of how serializable transactions differ from repeatable read: https://wiki.postgresql.org/wiki/SSI And of course, if you haven't already read the fine manual on the topic: https://www.postgresql.org/docs/current/static/mvcc.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql : could not serialize access due to read/write dependencies among transactions @ 2017-02-10 10:23 Gunnar "Nick" Bluth <gunnar.bluth.extern@elster.de> parent: Neslisah Demirci <neslisah.demirci@markafoni.com> 2 siblings, 0 replies; 4+ messages in thread From: Gunnar \"Nick\" Bluth @ 2017-02-10 10:23 UTC (permalink / raw) To: pgsql-admin Am 01/18/2017 um 05:54 AM schrieb Neslisah Demirci: > Hi , > > > I have problem about this issue ; > > > could not serialize access due to read/write dependencies among transactions That's something you have to expect when using SERIALIZABLE isolation level. Maybe re-read https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-SERIALIZABLE > A message app like whatsapp i decided to use isolation level > serializable if i use other transaction levels many conversations > started with same number (think about whatsapp web sidebar multipling > with same number every message). Are you not using a sequence for generating these IDs? As those are counting independent of transaction visibility... What you say sounds more like "SELECT max(conversation_id) + 1 AS new_conversation_id FROM ..." > I also add an index to my query and my query's execution plan don't use > seq scan . > > > How can i solve this issue ? Can i solve this on db orr on app ? a) switch to a sequence for generating these IDs (you can go back to a lower isolation level then). You may get holes in the IDs then (on rolled back transactions), but I don't see how that would not be acceptable for conversation IDs b) deal with it in the app (probably not what you want, think roud-trip time) > Nesliþah Demirci | Veritabaný Yöneticisi > > Ayazaða cad. No:4 Uniq Ýstanbul Plaza > B2 /Kat:3 34396 Ayazaða-SARIYER-Ýstanbul > T. (+90) 212 453 16 00 5516 > F. (+90) 212 453 16 16 > www.markafoni.com <http://www.markafoni.com/; > www.facebook.com/markafoni <http://www.facebook.com/markafoni; > blog.markafoni.com > > > Regards, -- Gunnar "Nick" Bluth DBA ELSTER Tel: +49 911/991-4665 Mobil: +49 172/8853339 Attachments: smime.p7s (application/pkcs7-signature, 4.0K) download ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-02-10 10:23 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-01-18 04:54 postgresql : could not serialize access due to read/write dependencies among transactions Neslisah Demirci <neslisah.demirci@markafoni.com> 2017-01-18 05:10 ` John R Pierce <pierce@hogranch.com> 2017-01-18 17:10 ` Kevin Grittner <kgrittn@gmail.com> 2017-02-10 10:23 ` Gunnar "Nick" Bluth <gunnar.bluth.extern@elster.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox