Table Locks and bulk creating inherited models

[Geoffrey Martin-Noble]

Is there a particular reason Django doesn't implement table locks? These are vendor-specific, but seem to be common to various SQL backends, which is something Django generally does well.

I am working on an application in which I would like to be able to perform bulk create on inherited models. I see the issue with not being able to fetch primary keys, but it seems that this could be accomplished by placing a table lock on both the parent and inherited table, determining the appropriate primary keys in python, circumventing a table autoincrement, and then bulk creating both models.

[Russell Keith-Magee]

Hi Geoffrey

On Tue, May 3, 2016 at 3:42 AM, Geoffrey Martin-Noble <gmnge...@gmail.com> wrote:

Is there a particular reason Django doesn't implement table locks? These are vendor-specific, but seem to be common to various SQL backends, which is something Django generally does well.

I’m not aware of any philosophical or technical reason why they haven’t been implemented - as far as I’m aware, it’s simply a matter of nobody has asked for them, and nobody has offered a patch for them. 

Over the last few years, we’ve been moving away from the Lowest Common Denominator model for the database backends, and towards a general philosophy of “if a database can do it, we will support it”. The only restriction on that is finding an elegant way to express the underlying idea in a way that can be opt-in, and is consistent with existing APIs.

Have you given any thought to what the API for a table lock would look like?

Yours,

Russ Magee %-)

[Curtis Maloney]

On 03/05/16 09:47, Russell Keith-Magee wrote:
> Hi Geoffrey

>
> Have you given any thought to what the API for a table lock would look like?

Since it's a table-wide action, having it on the Manager makes some
sense... though it would be, I suspect, implemented via _meta.

I can see something like:

with MyModel.objects.lock():
... do stuff ...

Being an intuitive, inoffensive and hookable API.

--
Curtis

[Gavin Wahl]

>     with MyModel.objects.lock():

>         ... do stuff ... 

This is misleading as it implies the lock is released when the context manager exists, but in postgres at least the lock will be held until the end of the transaction.

What advantage does implementing an ORM API for table locking have over `cursor.execute("LOCK TABLE mymodel")`?

[Curtis Maloney]

On 03/05/16 14:08, Gavin Wahl wrote:
> > with MyModel.objects.lock():
> > ... do stuff ...
>
> This is misleading as it implies the lock is released when the context
> manager exists, but in postgres at least the lock will be held until the
> end of the transaction.

Hah! Shows how much I know about table locking :P

> What advantage does implementing an ORM API for table locking have over
> `cursor.execute("LOCK TABLE mymodel")`?

Much the same as the rest of the ORM API : Abstraction and convenience.

Even if it's just so you can pass a ModelClass instead of having to know
the table name... and avoid having to access cursor directly.

Perhaps transaction.lock_table(model) since, as you say, it's related to
the term of the transaction?

--
Curtis

[Gavin Wahl]

I don't think any abstraction is possible. Postgres has 8 different table-level locking modes, in addition to advisory locks and 4 row-level lock modes. I would say any attempt to abstract that would remove functionality, and that would not be convenient.

We are already suffering from an over-abstraction of locking -- we have limited support for SELECT ... FOR UPDATE, but not SELECT ... FOR SHARE (not to mention FOR NO KEY UPDATE and FOR KEY SHARE, which to be honest probably aren't useful to call from an ORM). QuerySet.select_for_update has an option to enable NO WAIT, but not SKIP LOCKED.

[Cristiano Coelho]

In my opinion SELECT ... FOR UPDATE is already quite powerful to add locks. Might not be as good as a straight table lock, but gives you enough power to lock by rows (select some indexed columns, most dbs will lock only rows matching them) or select by a non indexed column which will probably end up in a whole table lock.
As said above, dbs have many vendor specific lock types that might get quite complicated to abstract without limiting functionallity.

[Rick Leir]

I feel that you are better off disabling autocommit and managing your own transactions, docs here:
https://docs.djangoproject.com/en/1.9/topics/db/transactions/
But no doubt I am missing something in your plans. You would need multiple transactions or you would have performance problems due to large transactions.
HTH -- Rick