django table locking
msoulier
I have a daemon process running using the Django ORM API to access/
modify tables in PostgreSQL. I just ran into an issue where it looks
like the process is keeping read-locks on the tables that it is
reading, which is preventing a subsequent write lock from granting.
Does the ORM API normally lock tables? If so, when are the locks
released?
Thanks,
Mike
Malcolm Tredinnick
Django doesn't do any explicit table locking, although there are
transactions involved. However, that shouldn't be affecting this.
SELECT statements require an "ACCESS SHARE" lock on a table and INSERT
or UPDATES take a "ROW EXCLUSIIVE" lock, which doesn't conflict with
"ACCESS SHARE". So selects from the same table in a different
transaction are possible to be concurrent with writes (the selects just
won't see the newly written data until that transaction is committed).
So there shouldn't be any lock waiting going on there.
You just say "read locks", but that isn't a defined postgreSQL lock
name, so can you be more specific? Look in the pg_locks system view when
this problem is going on and track down who is waiting on what locks and
it might help diagnose the problem a bit more.
Regards,
Malcolm
msoulier
wrote:
> transactions involved. However, that shouldn't be affecting this.
if you don't mind two users stepping on one another's changes, which
you would have to prevent with explicit, optimistic locking, I assume?
> You just say "read locks", but that isn't a defined postgreSQL lock
relname | relation | database | transaction | pid |
mode | granted
-------------+----------+----------+-------------+-------
+---------------------+---------
pg_class | 1259 | 17456 | | 12221 |
AccessShareLock | t
adminevents | 17818 | 17456 | | 31151 |
AccessShareLock | t
clients | 17618 | 17456 | | 10325 |
AccessExclusiveLock | f
clusternode | 17759 | 17456 | | 31151 |
AccessShareLock | t
pg_locks | 16759 | 17456 | | 12221 |
AccessShareLock | t
clients | 17618 | 17456 | | 31151 |
AccessShareLock | t
cluster | 17746 | 17456 | | 31151 |
AccessShareLock | t
So one process was waiting to acquire an AccessExclusiveLock, and
there was already an AccessShareLock on it (the clients table).
Mike
msoulier
> So one process was waiting to acquire an AccessExclusiveLock, and
> there was already an AccessShareLock on it (the clients table).
commit is taking place in postgres, as the locks don't seem to be
releasing.
I've had to remove the transaction middleware to prevent the locks
from being held forever.
Mike
Malcolm Tredinnick
On Wed, 2008-12-03 at 11:16 -0800, msoulier wrote:
> On Nov 18, 7:46 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
> wrote:
> > Django doesn't do any explicit table locking, although there are
> > transactions involved. However, that shouldn't be affecting this.
>
> So Django is not safe to use in a concurrent environment?
Concurrent means many different things. At some level, every application
(including many Django usages) involve concurrent usage. I guess from
your subsequent comments you are talking about simultaneous updates to
the same piece of data. That is not a really common case in web-based
applications, since (a) they're much more read- than write-oriented and
(b) even in writes-heavy situations, the writes tend to be spread out
across the entire dataset. Simultaneous updates to the same piece of
data are rare.
> Well, it is
> if you don't mind two users stepping on one another's changes, which
> you would have to prevent with explicit, optimistic locking, I assume?
As you no doubt realise, things like "select for update" calls really
have to made explicitly no matter whether you're doing it in raw SQL or
via some library API. This is because trying to work out which data
should be reserved as unchangeable until a subsequent update happens is
effectively impossible to work out and getting it wrong either leads to
disappointment or woeful performance (essentially serialised access).
For Django 1.1 we're looking at adding an API for specifying "SELECT FOR
UPDATE" behaviour, which will allow the developer to specify when they
want that to be in place. It's not entirely trivial, since we'd like to
avoid normal code being able to cause locked up situations (particularly
here in the land of reusable applications), but it's work in progress.
> > You just say "read locks", but that isn't a defined postgreSQL lock
>
> Sorry, my tables looked like this:
>
> relname | relation | database | transaction | pid |
> mode | granted
> -------------+----------+----------+-------------+-------
> +---------------------+---------
> pg_class | 1259 | 17456 | | 12221 |
> AccessShareLock | t
> adminevents | 17818 | 17456 | | 31151 |
> AccessShareLock | t
> clients | 17618 | 17456 | | 10325 |
> AccessExclusiveLock | f
> clusternode | 17759 | 17456 | | 31151 |
> AccessShareLock | t
> pg_locks | 16759 | 17456 | | 12221 |
> AccessShareLock | t
> clients | 17618 | 17456 | | 31151 |
> AccessShareLock | t
> cluster | 17746 | 17456 | | 31151 |
> AccessShareLock | t
>
> So one process was waiting to acquire an AccessExclusiveLock, and
> there was already an AccessShareLock on it (the clients table).
Okay, so you'll need to try and work out what is trying to get the
AccessExclusiveLock. That's normally only table changing operations and
vacuum analyze statements, from memory (the latter needs it to gather
accurate statistics). Normal Django operations won't trigger those
(well, some django-admin commands will -- see
django/core/management/commands/ -- but they aren't run in normal
operations). The AccessShare locks are likely coming from SELECT
statements and you'll no doubt have a bunch of those happening in any
normal operation.
It's really just that table-level exclusive lock that is the problem, so
try to work out what might be causing that. It has a clearly different
PID there, too (it's the only one with that PID), suggesting it really
is some distinct operation.
Regards,
Malcolm