maria-discuss team mailing list archive
Mailing list archive
Re: INSERT IGNORE (and other SQL commands) fail with: Deadlock found when trying to get lock;
That is going to lock ALL of the rows in `user` because index traversal
will cover the PRIMARY KEY over all rows looking for a match, and InnoDB
locks the rows it traverses.
split $USER into userid and domain components and do
userid = $USER_ID and domain = $USER_DOMAIN
Assuming you have a unique key on (USER_ID,DOMAIN) you will only lock one
row and conflicts will be avoided.
You can also try using READ COMMITTED instead of REPEATABLE READ if you
can't make that change, but you will still get some deadlocks.
On Thu, Dec 12, 2013 at 4:28 AM, Benoit Panizzon <benoit.panizzon@xxxxxx>wrote:
> Hi Alex
> > DELETE bug seems to be present only in MariaDB version of Galera
> > cluster, hopefully it will be fixed soon. If it is of any help, you
> > could upgrade to the latest upstream release from Codership.
> > INSERT bug is confirmed in the upstream and is tracked here:
> > https://bugs.launchpad.net/codership-mysql/5.6/+bug/1255147
> Thank you. I managed to work around those problems.
> Now a new 'Deadlock' problem arrises from time to time.
> When a user successfully logs in to the IMAP server, we register the
> of that last login:
> "UPDATE user set lastLogin=Now() where concat(userid,'@',domain)='$USER'"
> In about 4 of 5 cases, this is successfull.
> In one of 5 cases we get a Deadlock error.
> Any hints what causes that problem? Can't we user NOW() because not all of
> cluster members might have the same timestamp?
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp