Comments - Understanding replication and read lock contention problems

3 years, 3 months ago Daniel Black

First there is a need to clarify one thing, there is never any read lock contentions, all read locks are shared.

Replication is one way, master -> replica (slave). To identify the difference you'll need to look at the data in the columns.

The error you have posted is a dead-lock error. This occurs when a transaction takes locks (update/insert) in a different order to another transaction. So transaction X takes lock A and tries to obtain lock B. And transaction Y holds lock B and tries to aquire lock A. This is detected by the server and a deadlock thrown as there is no obvious server action to take. The locks would be output that you have posted, its just truncated slightly.

From your scenario it seems you'd like bi-direction replication between your end nodes and other nodes. For this scenario it seems Galera would be a better replication technology to use.

Bayes_token has a primary key of `(id,token)` so even with galera deadlocks may not be resolved (but at least it will be in sync). On this table small difference in the values may not make a difference to the application (https://github.com/apache/spamassassin/blob/trunk/sql/bayes_mysql.sql#L23) as I understand it.

 
3 years, 3 months ago Alex Regan

Thanks so much for your help and explanation.

Perhaps the solution for me right now is to configure all clients to read from their local copy and to write any changes directly to the primary? Would this avoid the deadlock issue?

 
3 years, 3 months ago Daniel Black

With galera, there is no primary. Updates to the local node are replicated to other members of the cluster. It won't necessary avoid deadlock. The deadlocks shows are count updates and I can't see the order that spam assassin is doing database updates. With an understanding of that you might be able to do a small code change to avoid it, or at least handle the deadlock (retry maybe).

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.