0

I've been having a few issues recently working with a large MySQL database within django, MySQL seems to be having issues with deadlocks.

I keep getting the error:

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

Usually this seems to happen when calling the save or create functions for a new model instance (e.g).

                payment = Payment.objects.create(
                    to_member=to_member,
                    to_name="%s" % to_member.username,
                    from_member=from_member,
                    from_name="%s" % from_member.username,
                    description="Resubscription",
                    type='resubscription',
                    amount=5.00,
                    currency=from_member,
                    processor='e-wallet'
                )

The particular table I'm trying to insert into has about 3.1 million rows and a size of 2.2 GB.

In production the application has quite a number of active users (Member). The Payment model has two particular foreign keys (to_creator and from_member). So I wondered if that might be the cause of the be the cause of the issue rather than just on the Payment model?

I've tried tweaking various aspect of the mysql configuration to manage this, but I seem to either get more deadlocks or the whole system locks up!

Below are my current settings for the locks:

Database locks

Any help would be greatly appreciated. I've been pulling my hair out over the last few days about this.

Dan

Daniel Askwith
  • 1

1 Answers1

0

First of all, is there any data loss? Error in the transaction means that it is not implemented and theoretically your data is fine.

Second - it's not a normal situation for such a small database (yes, I see that it is 2GB, and yes it is quite small, even the whole database could be cached in RAM).

Let's check: do you have overridden .save() method of the Payment model? If yes please tell us more about what you see there.

There is some blocking code inside already blocked code you need to find it.

fanni
  • 1,149
  • 8
  • 11
  • No, I'm not aware of any data loss. Just the transaction not being implemented. What we do have is multiple servers running the same application (using a load balancer), so potentially this could be causing an issue. No, the .save() method is not overwritten for this model. This was just an example of the error, it's actually occurring on multiple model creations. – Daniel Askwith Oct 14 '21 at 09:23
  • we need to know who is holding a lock, so let's look to SHOW ENGINE INNODB STATUS – fanni Oct 17 '21 at 23:31