maria-discuss team mailing list archive

[Benoit Panizzon <benoit.panizzon@xxxxxx>]

Hello dear fellow mariadb / gallera users.

It looks like I now found out what causes the deadlock. It's not the statement 
NOW() and also not the concat statement (that might be a performance issue, 
but we don't have that issue at the moment :-) )

I changed my 'update' script, to retry the transaction as advised by the 
MariaDB error. Well, every time I retry, the update fails with a deadlock...

Strange, are there some weird datetime values that are not being accepted?

So I started again, outputing the SQL statement:

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:09' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
Result: yes - 2013-12-19 14:55:09

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:16' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
Result: yes - 2013-12-19 14:55:16

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
Result: yes - 2013-12-19 14:55:17

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try 
restarting transaction
Result: no - 2013-12-19 14:55:17

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try 
restarting transaction
Result: no - 2013-12-19 14:55:17

SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where 
concat(userid,'@',domain)='benoit@xxxxxxxxxx'
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try 
restarting transaction
Result: no - 2013-12-19 14:55:17

Well, there I pinpointed the problem. If an update changes nothing (because I 
get two updates in the same second) the result is a deadlock. Of course a 
retry loop hangs, if the timestamp is not being updated during that loop.

Well, I now know I can just ignore this error in case of an update. But it is 
still very ugly as I have to make sure all of my skripts just ignore UPDATE 
MariaDB errors and don't just die.

Benoit Panizzon
-- 
I m p r o W a r e   A G    -    
______________________________________________________

Zurlindenstrasse 29             Tel  +41 61 826 93 07
CH-4133 Pratteln                Fax  +41 61 826 93 02
Schweiz                         Web  http://www.imp.ch
______________________________________________________