hi,
I’m busting my head on preventing script crashes on deadlocks. I’ve a quite busy python/django script that makes a lott of database queries INSERT, UPDATE and SELECT
The database is also queried by the php website.
To resolve a performance issue I added an index to one of the tables which speeds things up very much. This also results in the python script crashing regulary on a database deadlock. I suppose that’s only happening because queries are faster and thus ‘closer’ together and thus interferring much more.
All queries in the script are processed in the same way:
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 thing is, when I added the index, I got the same deadlock twice in 15 minutes:
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')
I
With SHOW ENGINE INNODB STATUS I found that it interferred with a query from the php website
LATEST DETECTED DEADLOCK
------------------------
120829 11:38:01
*** (1) TRANSACTION:
TRANSACTION 0 17739618, ACTIVE 0 sec, process no 22124, OS thread id 140621145618176 inserting
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 1
MySQL thread id 32731, query id 1936204 localhost root Sending data
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,
429 AS gps_unit_id, '2012-08-29 11:38:00' AS date,
50874760 AS lat,
4493660 AS lng
FROM gps_track_segments
INNER JOIN gps_tracks ON gps_track_segments.gps_track_id = gps_tracks.id
WHERE gps_tracks.hash = 'e7f1adcf5b36d454a55ff83449d4dcdac74b64044e733cdaafd7d2d0eb62862e'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 123972 n bits 408 index `gps_unit_date_idx` of table `freetrack`.`gps_points` trx id 0 17739618 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 000001b5; asc ;; 1: len 4; hex 5028e653; asc P( S;; 2: len 4; hex 0081526d; asc Rm;;
*** (2) TRANSACTION:
TRANSACTION 0 17739617, ACTIVE 0 sec, process no 22124, OS thread id 140621056968448 fetching rows, thread declared inside InnoDB 241
mysql tables in use 4, locked 4
312 lock struct(s), heap size 63472, 1302 row lock(s)
MySQL thread id 33766, query id 1936203 localhost root Copying to tmp table
CREATE TEMPORARY TABLE temp_connected_gps_units
SELECT GpsUnit.id
FROM gps_unit_connections AS GpsUnitConnection
INNER JOIN gps_units AS GpsUnit ON GpsUnit.id = GpsUnitConnection.unit_id
INNER JOIN latest_gps_points AS LatestGpsPoint ON LatestGpsPoint.gps_unit_id = GpsUnit.id
WHERE GpsUnitConnection.relation_id = 16
AND (( LatestGpsPoint.date BETWEEN GpsUnitConnection.startdate AND GpsUnitConnection.stopdate AND NOW() < GpsUnitConnection.stopdate)
OR (LatestGpsPoint.date > GpsUnitConnection.startdate AND GpsUnitConnection.stopdate IS NULL))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 123972 n bits 408 index `gps_unit_date_idx` of table `freetrack`.`gps_points` trx id 0 17739617 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 000001b5; asc ;; 1: len 4; hex 5028e653; asc P( S;; 2: len 4; hex 0081526d; asc Rm;;
Record lock, heap no 339 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 000001ad; asc ;; 1: len 4; hex 503de2da; asc P= ;; 2: len 4; hex 00950caf; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 114548 n bits 240 index `date` of table `freetrack`.`gps_points` trx id 0 17739617 lock mode S waiting
Record lock, heap no 172 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 503de2f8; asc P= ;; 1: len 4; hex 00950ceb; asc ;;
*** WE ROLL BACK TRANSACTION (1)
(I now the times don’t match, but I checked this serveral times and these types of errors go together)
As you can see, the first transaction is rolled back and this is from the python script. Which crashes. Sounds logical, right?
I also sometimes get the same deadlock but the transaction (2) is rolled back (the query from the php site) and then there’s no crash in the python script.
now, I also found another deadlock the same way. With two query’s from the python script. One of the queries was rolled back. But the script just continued.
How can that be? All queries are made like above. I dont understand this at all.
Why does it crash in one case and not in the other?