maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@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
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>