Re: database-level lockdown - Mailing list pgsql-general
| From | Filipe Pina |
|---|---|
| Subject | Re: database-level lockdown |
| Date | |
| Msg-id | 1436276633.23458.1@smtp.gmail.com Whole thread Raw |
| In response to | Re: database-level lockdown (Alban Hertroys <haramrae@gmail.com>) |
| Responses |
Re: database-level lockdown
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
On Ter, Jul 7, 2015 at 2:31 , Alban Hertroys <haramrae@gmail.com> wrote:
On 7 July 2015 at 12:55, Filipe Pina <filipe.pina@impactzero.pt> wrote:On Ter, Jul 7, 2015 at 1:41 , Adrian Klaver <adrian.klaver@aklaver.com> wrote:What Adrian was trying to get out of you is why you think you need those locks. You're working with an RDBMS, it does the job of keeping data integrity in a multi-user environment already. You can trust it do do that well. So unless you're doing something really special, you shouldn't need to lock anything. Among the special cases where you do need locks are things like needing a gapless sequence (invoice numbers come to mind); things like that. So once again, what do you need those locks for? P.S. Please don't top-post on this list. I had to edit your message to fix the flow of the conversation.Still not sure what is you are trying to accomplish. Is it really necessary that every transaction be serialized? Or to put it another way, why are you running in serializable by default? Or yet another way, what is the problem you are trying to solve with serialized transactions?Exactly, that's the twist I've decided after some tests yesterday (the "lock all tables on last try" degraded performance in an obscene way): giving up on serializable by default. I wanted to use serializable so developers wouldn't have to worry about properly using locks, but implementing this "serialization failure" handler is becoming a major headache...--If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
It probably wasn't clear but the conversation did start with exposing my issue, I'll try to rephrase.
Serializable keeps data integrity of the database, that's true. But it does that by "refusing" transactions that would compromise it (with error 40001).
I need to make sure no transactions fail due to such errors, so I made an handler to retry transactions if they fail due to 40001.
But I can't leave it retrying forever, so I have this (configurable) limit of 5 retries.
5 retries might be enough in some case, but it might not be enough in others.
So how do I make sure the last try would not fail with serialization error?
I could only come up with the idea of using LOCK database (on that last try) but as it is not available in PG, I went for "lock all tables".
Does it make sense now?
I was probably having the wrong approach to the problem anyway, I've went with "read committed" mode now and normal locking (SELECT .. FOR UPDATE on the required spots) and it works better..
I just come from a GTM background (and all the other developers in the project) and we're used to the TP RESTART concept where, in the final retry, GTM locks the database region(s) that were used by that transaction (not the whole DB, just the regions).
Thanks and sorry for the top-posting, not really used to mailing lists and proper posting styles..
pgsql-general by date: