Django Upsert Transaction

[Fendy Purnomo]

Hi,

I'm relatively new to Django and have use it in past few months.

Recently I stumbled upon a SQL deadlock. After digging deeper it was caused by SQL gap lock when inserting.

Then I found that Django upsert is actually putting update/insert inside one transaction. I might be missing what's the point of doing this but why don't we put it in different transaction?

I am really new to DBA stuffs and a bit lost where to start. Really glad for any kind of help! Thanks in advance :D

Regards,

Fendy

[Mike Morris]

what's the point of doing this

I assume it is "atomicity" -- making sure the  update/insert pair is indivisible.... and that it is impossible to do one without the other. Depending on application, that could be catastrophic...

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a9d77d3b-c538-4912-ad7b-b4ce2ea06b55%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James Schneider]

On Aug 25, 2017 5:45 AM, "Fendy Purnomo" <fendy....@gmail.com> wrote:

Hi,

I'm relatively new to Django and have use it in past few months.

Recently I stumbled upon a SQL deadlock. After digging deeper it was caused by SQL gap lock when inserting.

I'm assuming this was not in a Django app?

Then I found that Django upsert is actually putting update/insert inside one transaction. I might be missing what's the point of doing this but why don't we put it in different transaction?

Well, if the insert and update operations we're in separate transactions, that would make the transactions useless and a waste of resources.

Process 1 Transaction 1: Insert new object with ID 5.

Process 2 Transaction 1: Delete object with ID 5.

Process 1 Transaction 2: Update object with ID 5. (Failed)

May as well not use transactions at that point.

Doing both insert and update in the same transaction keeps Process 2 from interfering. Both steps would be done together sequentially as part of the first line above.

However, the transaction would need to close before another process could act upon that object (including retrieving it). That's a double-edged sword in some cases.

-James