Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cc8Q9-0005Vp-Gq for pgsql-admin@arkaria.postgresql.org; Fri, 10 Feb 2017 10:27:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cc8Q8-0003Fr-SK for pgsql-admin@arkaria.postgresql.org; Fri, 10 Feb 2017 10:27:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cc8OQ-0001MP-NG for pgsql-admin@postgresql.org; Fri, 10 Feb 2017 10:25:30 +0000 Received: from edmmai02.een.elster.de ([217.237.175.35]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84_2) (envelope-from ) id 1cc8OM-0000S8-23 for pgsql-admin@postgresql.org; Fri, 10 Feb 2017 10:25:30 +0000 X-IronPort-AV: E=Sophos;i="5.35,140,1484002800"; d="p7s'?scan'208";a="4650692" Received: from edmzrf02.een.elster.de ([192.168.20.13]) by edmmai02.een.elster.de with ESMTP/TLS/DHE-RSA-AES256-GCM-SHA384; 10 Feb 2017 11:25:24 +0100 Received: from [10.200.136.1] (unknown [10.200.136.1]) by edmzrf02.een.elster.de (Postfix) with ESMTPSA id 224533E0EEC for ; Fri, 10 Feb 2017 11:25:24 +0100 (CET) Subject: Re: postgresql : could not serialize access due to read/write dependencies among transactions To: pgsql-admin@postgresql.org References: From: "Gunnar \"Nick\" Bluth" X-Enigmail-Draft-Status: N1110 Message-ID: Date: Fri, 10 Feb 2017 11:23:20 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; protocol="application/pkcs7-signature"; micalg=sha-256; boundary="------------ms000409040303060009050602" X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a cryptographically signed message in MIME format. --------------ms000409040303060009050602 Content-Type: text/plain; charset=windows-1254 Content-Transfer-Encoding: quoted-printable Am 01/18/2017 um 05:54 AM schrieb Neslisah Demirci: > Hi ,=20 >=20 >=20 > I have problem about this issue ;=20 >=20 >=20 > could not serialize access due to read/write dependencies among transac= tions 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 . >=20 >=20 > 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=FEah Demirci | Veritaban=FD Y=F6neticisi=20 >=20 > Ayaza=F0a cad. No:4 Uniq =DDstanbul Plaza > B2 /Kat:3 34396 Ayaza=F0a-SARIYER-=DDstanbul > T. (+90) 212 453 16 00 =96 5516 > F. (+90) 212 453 16 16 > www.markafoni.com > www.facebook.com/markafoni > blog.markafoni.com >=20 >=20 >=20 Regards, --=20 Gunnar "Nick" Bluth DBA ELSTER Tel: +49 911/991-4665 Mobil: +49 172/8853339 --------------ms000409040303060009050602 Content-Type: application/pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCC DCQwggVwMIIEWKADAgECAgZIjQJd1vQwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNVBAYTAkRF MSswKQYDVQQKDCJULVN5c3RlbXMgRW50ZXJwcmlzZSBTZXJ2aWNlcyBHbWJIMR8wHQYDVQQL DBZULVN5c3RlbXMgVHJ1c3QgQ2VudGVyMSUwIwYDVQQDDBxULVRlbGVTZWMgR2xvYmFsUm9v dCBDbGFzcyAyMB4XDTEyMTEyOTExMjM1NVoXDTI0MTEyOTIzNTk1OVowdTELMAkGA1UEBhMC REUxJTAjBgNVBAoTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgxHzAdBgNVBAsTFlQt U3lzdGVtcyBUcnVzdCBDZW50ZXIxHjAcBgNVBAMTFVRlbGVTZWMgQnVzaW5lc3MgQ0EgMTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANEw1E1Ew+RLWrQYng5RJ7WpYkl6GgbA cywRiVWSrA7L/kgvu13j9KmIuoFr5Ps3HSxX60WYVUFGicfnpzFke554CJ/AGD/pSmKxhPzg 0MytlS67gGr1p2TFN/vePeegzbGidDWjz+/tTXRtuo0kGrSf/FpVRjv/98LxeXp2d89P68oi jXbYBZn3BPE8n3zbqGnwVd+jQahJS25dIHENuosJbmsi8KSd81aJwNbAWDAIV+Zi3Y4/0L3j ujhXX63SNVK7q67Gtp8hEpOvRYtWNtXMpEw2LzPhtILmZEVd7urX3KBNQX/eZpmK/V1u7EXr dOIJE/aK6qhnKGpOn/gSrLUCAwEAAaOCAfYwggHyMA4GA1UdDwEB/wQEAwIBBjAdBgNVHQ4E FgQUhoiTH4vrVZZl1UcBvTeMfM2ox6MwHwYDVR0jBBgwFoAUv1kgNgB5oKAia4zV8mHSuCzL gkowEgYDVR0TAQH/BAgwBgEB/wIBADBTBgNVHSAETDBKMD4GCSsGAQQBvUcNGTAxMC8GCCsG AQUFBwIBFiNodHRwOi8vc2JjYS50ZWxlc2VjLmRlL2Nwcy9jcHMyLnBkZjAIBgZngQwBAgIw gfoGA1UdHwSB8jCB7zA6oDigNoY0aHR0cDovL2NybC5zYmNhLnRlbGVzZWMuZGUvcmwvR2xv YmFsUm9vdF9DbGFzc18yLmNybDCBsKCBraCBqoaBp2xkYXA6Ly9sZGFwLnNiY2EudGVsZXNl Yy5kZS9DTj1ULVRlbGVTZWMlMjBHbG9iYWxSb290JTIwQ2xhc3MlMjAyLE9VPVQtU3lzdGVt cyUyMFRydXN0JTIwQ2VudGVyLE89VC1TeXN0ZW1zJTIwRW50ZXJwcmlzZSUyMFNlcnZpY2Vz JTIwR21iSCxDPURFP0F1dGhvcml0eVJldm9jYXRpb25MaXN0MDoGCCsGAQUFBwEBBC4wLDAq BggrBgEFBQcwAYYeaHR0cDovL29jc3AwNC50ZWxlc2VjLmRlL29jc3ByMA0GCSqGSIb3DQEB CwUAA4IBAQCThdHrhHoZWcglgDokpbmWcQab/tYOdOBA3dXNXR0ggBlDSQ94dt4j3lzMN+2j ynxKIQTZJZGZsT8HsmRC3BTnpY/T7a0gbXkdUiwXspLQs18wL0Q+Vu80V7aHkKm8R3WIMpM+ 72lLGkowDbHnDUMBZYd8N4+bStWrUcn29ZfDHj9eFJSutg1yWfzl1xdfxOJmTtpmjSjRCtc7 tixaok7W2O+SlS1lmSRoMIqCDWLO34f//Dj1hHrOx4xxRnojKX2s9lYoAqyAi69XoEgdiGGf Sj7/37yhZp0aAYbzHBFVy1rS/DC5FNYrCQfsauP2jVEaRClHbpeOwTHWNQj4HiRPMIIGrDCC BZSgAwIBAgIJAOgv2m5i1ojKMA0GCSqGSIb3DQEBCwUAMHUxCzAJBgNVBAYTAkRFMSUwIwYD VQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR8wHQYDVQQLExZULVN5c3RlbXMg VHJ1c3QgQ2VudGVyMR4wHAYDVQQDExVUZWxlU2VjIEJ1c2luZXNzIENBIDEwHhcNMTcwMjAx MTI1MjI4WhcNMjAwMjAxMjM1OTU5WjCBujELMAkGA1UEBhMCREUxKzApBgNVBAoMIkJheWVy aXNjaGVzIExhbmRlc2FtdCBmw7xyIFN0ZXVlcm4xEjAQBgNVBAsTCWVsc3Rlci5kZTEZMBcG A1UECxMQTWFpbC1aZXJ0aWZpa2F0ZTEVMBMGA1UEAxMMR3VubmFyIEJsdXRoMSwwKgYJKoZI hvcNAQkBFh1ndW5uYXIuYmx1dGguZXh0ZXJuQGVsc3Rlci5kZTEKMAgGA1UEBRMBMTCCASIw DQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMOv2jzaRaBBdpWKT/iwVk6M7BjwFOLjGi+z sfbuaiXE57PQqOy2+TRR+78VQk9pV1vb8UErJvqi/gdpizGk8+zyjcqhVVYKKwvLUWEIeOiT RGH+ueA14K/ljuhT2W6SbaUUom/Ln0AJC4S747raNXo8qVILuymzqAJIE4hrU0HaN2vEG4aI cCldrlNHarX5g3yXYllNRS9A29jVo0c6wRuWLt4hy4S0mxtpTbuFAOV17o2QTwRDQGAukf6x wynBQlKYVp4Mg5Mpj30N2sii6R4e+XDc8FrpJw4wmgqbPWbpFkUdiLuqeScARqY5F3DELmwc dlO1AJ8CmBw/r8tCSxsCAwEAAaOCAvcwggLzMB8GA1UdIwQYMBaAFIaIkx+L61WWZdVHAb03 jHzNqMejMB0GA1UdDgQWBBQ5Cl1MmzPCejbOjWyM1Bmw/6vFHjAOBgNVHQ8BAf8EBAMCBaAw TgYDVR0gBEcwRTBDBgkrBgEEAb1HDRkwNjA0BggrBgEFBQcCARYoaHR0cDovL3NiY2EudGVs ZXNlYy5kZS9kb3dubG9hZC9jcHMyLnBkZjAJBgNVHRMEAjAAMIHwBgNVHR8EgegwgeUwPaA7 oDmGN2h0dHA6Ly9jcmwuc2JjYS50ZWxlc2VjLmRlL3JsL1RlbGVTZWNfQnVzaW5lc3NfQ0Ff MS5jcmwwgaOggaCggZ2GgZpsZGFwOi8vbGRhcC5zYmNhLnRlbGVzZWMuZGUvQ049VGVsZVNl YyUyMEJ1c2luZXNzJTIwQ0ElMjAxLE9VPVQtU3lzdGVtcyUyMFRydXN0JTIwQ2VudGVyLE89 VC1TeXN0ZW1zJTIwSW50ZXJuYXRpb25hbCUyMEdtYkgsQz1ERT9DZXJ0aWZpY2F0ZVJldm9j YXRpb25MaXN0MIIBJwYIKwYBBQUHAQEEggEZMIIBFTAvBggrBgEFBQcwAYYjaHR0cDovL29j c3AwMy5zYmNhLnRlbGVzZWMuZGUvb2NzcHIwRAYIKwYBBQUHMAKGOGh0dHA6Ly9jcmwuc2Jj YS50ZWxlc2VjLmRlL2NydC9UZWxlU2VjX0J1c2luZXNzX0NBXzEuY2VyMIGbBggrBgEFBQcw AoaBjmxkYXA6Ly9sZGFwLnNiY2EudGVsZXNlYy5kZS9jbj1UZWxlU2VjJTIwQnVzaW5lc3Ml MjBDQSUyMDEsb3U9VC1TeXN0ZW1zJTIwVHJ1c3QlMjBDZW50ZXIsbz1ULVN5c3RlbXMlMjBJ bnRlcm5hdGlvbmFsJTIwR21iSCxjPWRlP2NBQ2VydGlmaWNhdGUwKAYDVR0RBCEwH4EdZ3Vu bmFyLmJsdXRoLmV4dGVybkBlbHN0ZXIuZGUwDQYJKoZIhvcNAQELBQADggEBAKZlYP/Xuqb5 DEwFRzdCeqCYoBWAO/U4dhEAkxHZEIuJSFLg3df6+p4i8CasI84FTtsDVbXxnsoFYXBWtk6A L8cQKL/b/VA8idZpNrIHAc0E4Eowzy4p+Q0a17CwN0E6RItw9Slr/oBflhEJWixCtoPoFRDD UMr0XE0EwIchQn2yzMnU5jZSu2MRdxr1fIl89g7Cw0XDUM/wIJg7klZtKTdmc69f5LBjI08W v5EF20oeQAHobnWLS5VjVTtzLYGME4tgGvpD8bg4y97PtDFhMg4YW2yKm3SXqd/w9StVmjdS W5IWyhqO2uDgvcdxGj0TRuYg02J1F7UxoXUeGYr8CZgxggO3MIIDswIBATCBgjB1MQswCQYD VQQGEwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEfMB0GA1UE CxMWVC1TeXN0ZW1zIFRydXN0IENlbnRlcjEeMBwGA1UEAxMVVGVsZVNlYyBCdXNpbmVzcyBD QSAxAgkA6C/abmLWiMowDQYJYIZIAWUDBAIBBQCgggIFMBgGCSqGSIb3DQEJAzELBgkqhkiG 9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTE3MDIxMDEwMjMyMFowLwYJKoZIhvcNAQkEMSIEICS7 JU06INKRu7M2NyZ6iZ0QwXPBRFQ3k5wOTG/Ro5+TMGwGCSqGSIb3DQEJDzFfMF0wCwYJYIZI AWUDBAEqMAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZI hvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgZMGCSsGAQQBgjcQBDGBhTCBgjB1 MQswCQYDVQQGEwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEf MB0GA1UECxMWVC1TeXN0ZW1zIFRydXN0IENlbnRlcjEeMBwGA1UEAxMVVGVsZVNlYyBCdXNp bmVzcyBDQSAxAgkA6C/abmLWiMowgZUGCyqGSIb3DQEJEAILMYGFoIGCMHUxCzAJBgNVBAYT AkRFMSUwIwYDVQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR8wHQYDVQQLExZU LVN5c3RlbXMgVHJ1c3QgQ2VudGVyMR4wHAYDVQQDExVUZWxlU2VjIEJ1c2luZXNzIENBIDEC CQDoL9puYtaIyjANBgkqhkiG9w0BAQEFAASCAQAZoOThW9kjgN5G2qc/TblZq9C3GHBzsvqI qOpjFX6bnbOpDZiKWMTLMNYdLEPkzInbExFzYu2vq0LBNk+uz1ZXisRa1t7WyZtuETFJD+0Y jTsuV28Jug7eoaDh0jeaRBybX7Dh9aM12uy7yr9Z4tGYjJziioyqdaJaHGlnM5Pz1PBN0YGW pzybIDQvxOWwN/63Bddvxs4SaI9u16jNG88mNsrHmv6H5pCH0z7bDSHJEjoFZhXvYGD6ruPr BOeRerxrxoNuZzf8PB6bAwGOvdWp3qSKLTNc3H9NXamZiJT7al07ww0ax4IGxD5UQzT5G3JT Ts7cyZydrSN2D52xx2RyAAAAAAAA --------------ms000409040303060009050602--