Re: could not serialize access due to concurrent update

#1

"Martin v. Löwis" <martin@v.loewis.de> wrote:

Quote:

I'm using psycopg, and accessing a 7.4 database. I get
the error mentioned in the subject, and I cannot figure
out what the problem is.

Roughly, my code reads

self.cursor.execute("lock table browse_tally")
if self.is_cache_current():
# overlapping update, just release the lock
self.store.commit()
return
self.cursor.execute("delete from browse_tally")
...
self.cursor.execute("update timestamps set value=now() where
name='browse_tally'")
self.store.commit()

The psycopg exception I get refers to the "delete from"
statement. This fragment is the only code updating the
table, but multiple CGI processes may run the same code.

I would have expected that the "lock table browse_tally"
prevents multiple simultaneous accesses to the table,
so that the second attempt at running the same code (which
will always regenerate the table from scratch - it's a
cache) should block until the previous update completes.

For some reason, this does not work. Any insight
appreciated.

Your reasoning is correct.

Unfortunately I have no knowledge of Python or psycopg, but the manual
seems to suggest that there is no autocommit mode in psycopg. Is that
correct?

I'm asking because one possible explanation would be that autocommit
is active and each statement runs in its own transaction. That would
explain why LOCK TABLE seems to have no effect.

Another explanation I can think of is that the DELETE also affects rows
in other tables. This can happen if you have foreign key constraints
pointing to this table that have ON DELETE UPDATE/SET NULL/SET DEFAULT set.

The error message suggests that you use isolation level serializable.
Is that intentional?

Yours,
Laurenz Albe

#2

["Martin v. Löwis" has problem with concurrent updates although the
table is supposedly locked.]

Hmm, if that all is ok, I'm out of options for guessing.

What I would do at this point is turn on statement tracing on the server:

log_min_messages = debug5
log_min_error_statement = debug5

and examine what statements actually arrive at the server.

This is probably tough if many statements are generated.

It helps to have %c (session id) and %x (transaction id) in log_line_prefix.

Check for the following:

- Is there a BEGIN (or START TRANSACTION) before the LOCK TABLE, and
is the transaction id the same for both statements?

- Is the DELETE statement that fails due to concurrent update in the same
transaction?

Yours,
Laurenz Albe

#3

"Martin v. Löwis" <martin@v.loewis.de> wrote:

Quote:

Thanks for the help; with the help of somebody else, I tracked down the
problem: There was an additional write (of a time-stamp) to a separate
table , so the sequence performed by each writer was this:

BEGIN;
SELECT value FROM timestamps WHERE name='browse_tally';
/* Make a decision to regenerate the table; if yes: */
LOCK TABLE browse_tally;
DELETE FROM browse_tally;
/* compute new value of tally, by reading various tables */
COPY TO browse_tally;
UPDATE timestamps SET value=NOW() WHERE name='browse_tally';
END;

So one process got the browse_tally lock, the other one blocked,
still holding a reader lock for the timestamp. When the writer
tried to upgrade its reader lock for the timestamps to a
writer lock, postgres broke the deadlock. My solution is
to change this to

BEGIN;
SELECT value FROM timestamps WHERE name='browse_tally';
END;

BEGIN;
/* Make a decision to regenerate the table; if yes: */
LOCK TABLE browse_tally;
DELETE FROM browse_tally;
/* compute new value of tally, by reading various tables */
COPY TO browse_tally;
UPDATE timestamps SET value=NOW() WHERE name='browse_tally';
END;

I'm still puzzled that the error is reported when it
attempts to perform the DELETE FROM; I would have understood
if breaking the deadlock caused the LOCK TABLE to fail.

It is indeed mysterious why your change should change anything...

There is no read lock in PostgreSQL, so the 'SELECT value FROM timestamps'
should not result in a lock at all (unless you have unexpected things
like an INSTEAD OF SELECT rule that actually updates something...).

You can have a look at all current locks in pg_catalog.pg_locks.

If you use the default ACCESS EXCLUSIVE table lock, any other transactions
are blocked from accessing the table.

It is indeed very strange if you really get the deadlock in the DELETE
statement.

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: could not serialize access due to concurrent update

comp.databases.postgresql comp.databases.postgresql

Re: could not serialize access due to concurrent update - 05-09-2007 , 02:24 AM

Re: could not serialize access due to concurrent update - 05-11-2007 , 04:19 AM

Re: could not serialize access due to concurrent update - 05-14-2007 , 03:40 AM