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)='ben...@ambonet.ch' Result: yes - 2013-12-19 14:55:09 SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:16' where concat(userid,'@',domain)='ben...@ambonet.ch' Result: yes - 2013-12-19 14:55:16 SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where concat(userid,'@',domain)='ben...@ambonet.ch' Result: yes - 2013-12-19 14:55:17 SQL Query: UPDATE user set lastLogin='2013-12-19 14:55:17' where concat(userid,'@',domain)='ben...@ambonet.ch' 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)='ben...@ambonet.ch' 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)='ben...@ambonet.ch' 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 ______________________________________________________ _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
