Hi, To be able to help you more I need additional information, I assume that you do not have autocommit=1, but could you share show create table user ? R: Jan
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@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)='benoit@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)='benoit@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)='benoit@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)='benoit@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)='benoit@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
-- -- Jan Lindström Principal Engineer MariaDB | MaxScale | skype: jan_p_lindstrom www.skysql.com <http://www.skysql.com/> Twitter <http://twitter.com/skysql> Blog <http://www.skysql.com/blog/> Facebook <http://www.facebook.com/skysql> LinkedIn <http://www.linkedin.com/company/1214250> Google+ <https://plus.google.com/117544963211695643458/posts>
