Re: [Maria-discuss] INSERT IGNORE (and other SQL commands) fail with: Deadlock found when trying to get lock;
Hi
If memory serves me, the first timestamp, will be set to current universal time. Like db.table.last_changed
Ok, I replaced NOW() with a generated timestamp to avoid that NOW() deadlock. sub mysql_now() { my($sec,$min,$hour,$mday,$mon,$year,$wday, $yday, $isdst)=localtime(time); my($result)=sprintf("%4d-%02d-%02d %02d:%02d:%02d",$year+1900,$mon+1, $mday,$hour,$min,$sec); return $result; } [...] my $mysqltimestamp = mysql_now(); my $timestamph = $dbh->prepare("UPDATE user SET lastActive = ". $dbh-
quote($mysqltimestamp) ." WHERE userid = ". $dbh->quote($user) ." and domain = ". $dbh->quote($domain)); $timestamph->execute();
Still in about 1 of 10 updates, this causes that wrenched Deadlock error, causing the email not being processed correctly and in the case the email is not for local but being forwarded, the email is not forwarded to the correct destination. Dec 18 14:12:34 asterix mimedefang-multiplexor[8019]: NOQUEUE: Slave 0 stderr: DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at /etc/mimedefang-filter.mx line 316. Is there any known way to circumvent that Deadlock Problem? Kind regards 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 ______________________________________________________
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 -- 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 ______________________________________________________
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>
Hi Jan
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 ?
I did not set any autocommit statements. So I assume it's 1 as per default. Sure: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(30) DEFAULT NULL, `passwd` varchar(30) DEFAULT NULL, `domain` varchar(30) DEFAULT NULL, `homeserver` varchar(30) DEFAULT NULL, `quotaInMiB` int(11) DEFAULT '100', `password_type` varchar(10) DEFAULT 'plain', `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', `spampoint` decimal(5,1) DEFAULT '5.2', `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', `keepFwdCopy` tinyint(1) DEFAULT '0', `quotaUsed` int(11) DEFAULT '0', `extRef` varchar(12) DEFAULT NULL, `mandate` int(11) DEFAULT '0', `FwdBounceCount` decimal(1,0) DEFAULT '0', `FwdActive` tinyint(1) DEFAULT '1', `lastActive` datetime DEFAULT '0000-00-00 00:00:00', `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', `disabled` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `userid` (`userid`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=26944 DEFAULT CHARSET=utf8 Kind regards 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 ______________________________________________________
Hi, I do not follow why you get deadlock is autocommit is not off. Is this Galera ? R: Jan
Hi Jan
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 ? I did not set any autocommit statements. So I assume it's 1 as per default.
Sure:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(30) DEFAULT NULL, `passwd` varchar(30) DEFAULT NULL, `domain` varchar(30) DEFAULT NULL, `homeserver` varchar(30) DEFAULT NULL, `quotaInMiB` int(11) DEFAULT '100', `password_type` varchar(10) DEFAULT 'plain', `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', `spampoint` decimal(5,1) DEFAULT '5.2', `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', `keepFwdCopy` tinyint(1) DEFAULT '0', `quotaUsed` int(11) DEFAULT '0', `extRef` varchar(12) DEFAULT NULL, `mandate` int(11) DEFAULT '0', `FwdBounceCount` decimal(1,0) DEFAULT '0', `FwdActive` tinyint(1) DEFAULT '1', `lastActive` datetime DEFAULT '0000-00-00 00:00:00', `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', `disabled` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `userid` (`userid`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=26944 DEFAULT CHARSET=utf8
Kind regards
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>
Hi Thanks for your quick replies btw.
I do not follow why you get deadlock is autocommit is not off. Is this Galera ?
Yes 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 ______________________________________________________
Hello For those with the same problem, I found a completely stupid solution, which works! update user set lastlogin = IF(lastlogin != NOW(),NOW(),NOW()) where concat(userid,'@',domain)='$USER' For those now knowing what IF does... If lastlogin is not NOW() update it and set it to NOW() else set it to NOW() Well actualy this does exactly the same as update user set lastlogin = NOW() but surprisingly does not lead to the deadlock error, if lastlogin is already NOW(). Mit freundlichen Grüssen 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 ______________________________________________________
I would suggest turning on the InnoDB lock monitor (or at least look at the ratio of locks held vs redo entries in SHOW ENGINE INNODB STATUS) to see what locks the UPDATE is holding. --Justin On Thu, Dec 19, 2013 at 8:07 AM, Benoit Panizzon <benoit.panizzon@imp.ch>wrote:
Hello
For those with the same problem, I found a completely stupid solution, which works!
update user set lastlogin = IF(lastlogin != NOW(),NOW(),NOW()) where concat(userid,'@',domain)='$USER'
For those now knowing what IF does...
If lastlogin is not NOW() update it and set it to NOW() else set it to NOW()
Well actualy this does exactly the same as update user set lastlogin = NOW() but surprisingly does not lead to the deadlock error, if lastlogin is already NOW().
Mit freundlichen Grüssen
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
Hi, Could you please describe you galera setup, MySQL and galera versions, and how many nodes and configuration of the nodes. I could not repeat your problem using 2-node galera cluster as seen below: jan@jan-GE70-0NC-0ND ~/mysql/galera-5.5 $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/galera-cluster-5.5/node0/mysql.4000.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.34-MariaDB-wsrep-debug-log Source distribution, wsrep_23.7.6.r3457 Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test]> CREATE TABLE `user` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `userid` varchar(30) DEFAULT NULL, -> `passwd` varchar(30) DEFAULT NULL, -> `domain` varchar(30) DEFAULT NULL, -> `homeserver` varchar(30) DEFAULT NULL, -> `quotaInMiB` int(11) DEFAULT '100', -> `password_type` varchar(10) DEFAULT 'plain', -> `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', -> `spampoint` decimal(5,1) DEFAULT '5.2', -> `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', -> `keepFwdCopy` tinyint(1) DEFAULT '0', -> `quotaUsed` int(11) DEFAULT '0', -> `extRef` varchar(12) DEFAULT NULL, -> `mandate` int(11) DEFAULT '0', -> `FwdBounceCount` decimal(1,0) DEFAULT '0', -> `FwdActive` tinyint(1) DEFAULT '1', -> `lastActive` datetime DEFAULT '0000-00-00 00:00:00', -> `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', -> `disabled` tinyint(1) DEFAULT '0', -> PRIMARY KEY (`id`), -> UNIQUE KEY `userid` (`userid`,`domain`) -> ) ENGINE=InnoDB AUTO_INCREMENT=26944 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.20 sec) MariaDB [test]> MariaDB [test]> insert into user(id, userid, domain) values (1, 'jan','test'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> insert into user(id, userid, domain) values (5, 'benoit','ambonet.ch'); Query OK, 1 row affected (0.01 sec) MariaDB [test]> insert into user(id, userid, domain) values (7, 'kerr','test'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> commit work; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:09' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:16' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0
Hello
For those with the same problem, I found a completely stupid solution, which works!
update user set lastlogin = IF(lastlogin != NOW(),NOW(),NOW()) where concat(userid,'@',domain)='$USER'
For those now knowing what IF does...
If lastlogin is not NOW() update it and set it to NOW() else set it to NOW()
Well actualy this does exactly the same as update user set lastlogin = NOW() but surprisingly does not lead to the deadlock error, if lastlogin is already NOW().
Mit freundlichen Grüssen
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>
Hi, Remember that in Galera, replication beats write locks. If you want to avoid the deadlock ensure that updates to the table happen from only one node, otherwise you simply have to retry the transaction. this is the case with Galera or regular innodb. Your application must be able to handle deadlocks. You must make sure there is an index on (user_id,domain) or you will still lock the table. So either send all SQL that changes data through a single node (you can still failover to another node for HA) or deal with deadlocks, though in reality you have to deal with deadlocks in either case. --Justin
participants (3)
-
Benoit Panizzon -
Jan Lindström -
Justin Swanhart