CODEWITHSUNDEEP
pythonmysqldjangodeadlock
Jeroen
Jeroen

Reputation: 1638

Python/Django script crashes on MySQL deadlock

The following peace of script holds with error due to a deadlock:

sqlQuery = """INSERT INTO gps_points (gps_track_segment_id, gps_unit_id, date, lat,  lng) SELECT MAX(gps_track_segments.id) AS gps_track_segment_id, %(gps_unit_id)s AS gps_unit_id, %(date)s AS date, %(lat)s AS lat, %(lng)s AS lng FROM gps_track_segments 
    INNER JOIN gps_tracks ON gps_track_segments.gps_track_id = gps_tracks.id WHERE gps_tracks.hash = %(track)s"""

from django.db import connection, transaction
cursor = connection.cursor()
success = cursor.execute(sqlQuery, point)
transaction.commit_unless_managed()

The errorlog shows:

2012-08-28 12:37:58,051 - django.db.backends - DEBUG - (0.018) INSERT INTO gps_points (gps_track_segment_id, gps_unit_id, date, lat, lng) SELECT MAX(gps_track_segments.id) AS gps_track_segment_id, 121 AS gps_unit_id, '2012-08-28 12:37:56' AS date, 51361100 AS lat, 4983910 AS lng FROM gps_track_segments 
    INNER JOIN gps_tracks ON gps_track_segments.gps_track_id = gps_tracks.id WHERE gps_tracks.hash = '7f5d950564786e182e175fb5d8e1b937528f85cc1ddabbee0d53859fb603ede3'; args={'gps_unit_id': 121L, 'lat': 51361100, 'date': '2012-08-28 12:37:56', 'course': None, 'track': u'7f5d950564786e182e175fb5d8e1b937528f85cc1ddabbee0d53859fb603ede3', 'speed': '0.0', 'lng': 4983910, 'segment': 5, 'altitude': None, 'accuracy': None}
Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/lib64/python2.6/threading.py", line 532, in __bootstrap_inner
self.run()
  File "/usr/lib64/python2.6/threading.py", line 484, in run
self.__target(*self.__args, **self.__kwargs)
  ...
  ...
  File "/tcpserver/gpslibs.py", line 75, in saveGpsPoint
success = cursor.execute(sqlQuery, point)
  File "/usr/lib/python2.6/site-packages/django/db/backends/util.py", line 40, in execute
return self.cursor.execute(sql, params)
  File "/usr/lib/python2.6/site-packages/django/db/backends/mysql/base.py", line 114, in execute
return self.cursor.execute(query, args)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
DatabaseError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

With the mysql command SHOW ENGINE INNODB STATUS I saw that the above query conflicts with a query requested by the php website. The thing is that sometimes the php-request is rolled back and sometimes the INSERT. The last case results in the script stopping with the deadlock error. Since it's very difficult to change the query's involved. I like to now if:

Can I somehow make a Django/Python script that doesn't fail on the deadlock and just tries again? Ofcouse I don't realy care about the php request failing or getting a rollback. It's easy to handle that from a usuability point of view. I do care about the back-end script failing. This needs to work above all else

Upvotes: 2

Views: 2718

Answers (1)

Burhan Khalid
Burhan Khalid

Reputation: 174622

Can I somehow make a Django/Python script that doesn't fail on the deadlock and just tries again?

You can catch the exception raised DatabaseError and then perform your logic of executing the transaction again (perhaps by acquiring a write lock on the table first before inserting the row).

try:
    success = cursor.execute(sqlQuery, point)
except DatabaseError:
     # retry here

You should ideally wrap it in a method that you can call again.

Upvotes: 2

Related Questions