Re: Another psycopg crash: "ERROR: could not serialize access due to concurrent update"

Please address your replies to the list, so other people can benefit
from the answers.

On Fri, Apr 24, 2009 at 10:42 AM, Nathan <[email protected]> wrote:
> On Thu, Apr 23, 2009 at 6:49 PM, James Henstridge <[email protected]> wrote:
>> On Fri, Apr 24, 2009 at 8:21 AM, Nathan <[email protected]> wrote:
>>> Here's another problem I'm facing, though this one is less
>>> reproducible.  This sometimes occurs, and sometimes doesn't, which is
>>> odd--especially since I'm not doing anything fancy like threads--just
>>> straightforward loops.  I don't think this is 2.0.10-specific, since
>>> it occasionally happened with earlier versions, but the frequency of
>>> the crash has increased enormously (from about 2% of the time to
>>> 50-60% of the time) when I upgraded from 2.0.8 to 2.0.10.
>>>
>>> Does the info I included below ring a bell for anyone?
>>
>> That isn't a crash.  It means that you are using serialised
>> transactions, and the database can't process the concurrent
>> transactions in a way to give the appearance that they are running in
>> series.  This sort of error is part of life when you're using
>> serialisable transaction isolation.
>
> That's odd.  I've never heard transaction isolation levels before, so
> I read this page to educate myself about it:
>
> http://www.postgresql.org/docs/8.2/static/transaction-iso.html
>
> But according to section 12.2.1 on that page:
>
> "Read Committed is the default isolation level in PostgreSQL."
>
> ...and double-checking my config file shows that I have not customized
> any of the values in that section (I had no idea what they did in the
> first place, so why would I change them?):
>
> #---------------------------------------------------------------------------
> # CLIENT CONNECTION DEFAULTS
> #---------------------------------------------------------------------------
>
> # - Statement Behavior -
>
> #search_path = '"$user",public'   # schema names
> #default_tablespace = ''    # a tablespace name, '' uses
>          # the default
> #check_function_bodies = on
> #default_transaction_isolation = 'read committed'
> #default_transaction_read_only = off
> #statement_timeout = 0      # 0 is disabled
> #vacuum_freeze_min_age = 100000000
>
> So given that this is a fact of life when dealing with the
> serializable transaction isolation level, what does it have to do with
> me and my read committed transaction isolation level?

You can still experience serialisation failures with read committed
transactions.  They are less likely to occur due to the lower level of
isolation but still possible.  It won't exclusively lock resources in
as many situations, but it is still possible for two connections to
deadlock, and when that happens the database will abort one of the
transactions.


>> The usual way to handle this sort of error is to roll back and then
>> retry the transaction.  I am a little surprised that you're getting a
>> ProgrammingError rather than the more specific
>> TransactionRollbackError here though -- I added the code to raise a
>> specific subclass to make it easier to catch errors where you need to
>> retry.
>
> I'm surprised that I see it here, and nowhere else in my dozens of
> python, ruby, and other scripts that access the database.
>
> Perhaps the issue here is that this script has more cursors doing more
> work than most of my other scripts.  I have three different cursors,
> and they each do a lot of different things to a lot of different rows
> in several tables before I finally call commit() on the database
> connection at the very end of the script.  These operations don't
> necessarily need to be executed atomically as a batch, so perhaps I
> should just sprinkle some db.commit()'s around my loops and see if the
> problem goes away.  I wrote that script many years ago, and since then
> I typically execute commit() more often in my newer scripts.
> Thoughts?

Given that these errors are related to concurrent access to the
database, it is possible that the problem is in some other script.

You can monitor what is going on inside the database with the
following query in psql:

    select usename, current_query, waiting, procpid from pg_stat_activity;

If you see any connections listed as being "IDLE IN TRANSACTION", then
they are candidates for the cause of the problem.  They may be holding
locks on resources the cancelled transaction needed, and the database
decided to let that connection complete instead.  Connections that are
just "IDLE" shouldn't be a problem though -- they won't be holding any
locks.

James.