Hi,

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.

--Justin

On Thu, Dec 12, 2013 at 4:28 AM, Benoit Panizzon <benoit.panizzon@imp.ch> 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 timestamp
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 the
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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp