Re: database-level lockdown

On Ter, Jul 7, 2015 at 2:51 , Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 07/07/2015 06:44 AM, Filipe Pina wrote:
>> On Ter, Jul 7, 2015 at 2:31 , Alban Hertroys <haramrae(at)gmail(dot)com>
>> wrote:
>>> On 7 July 2015 at 12:55, Filipe Pina <filipe(dot)pina(at)impactzero(dot)pt>
>>> wrote:
>>>
>>> On Ter, Jul 7, 2015 at 1:41 , Adrian Klaver
>>> <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>> 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...
>>>
>>> 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.
>>> --
>>> 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..
>
> You also mentioned that this for a Django application, have you
> looked at its new(since 1.6) transaction management?:
>
> https://docs.djangoproject.com/en/1.8/topics/db/transactions/
>
> It allows you fairly fine grained control over transactions,
> including access to savepoints.
>
>
>> 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..
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com

Yes, I was using transation.atomic in some django-only operations, but
the problem is that some django operations call PG functions that call
other PG functions and the purpose was to have the django code
"agnostic" of the what the functions do..

But we've switched to "read committed" plus proper "FOR UPDATE"
statements in the few functions we have at the moment and the
performance is actually quite better (at the little extra cost of
people being careful with locks).