I don't have the answer to your exact question off the top of my head, but deadlocks like this are not specific to Galera - they can happen in standalone node, even if the window of opportunity for deadlocks is much greater with Galera.

The point I'm getting to is that if the application can't handle deadlocks, it is broken and arguably unfit for purpose - because deadlocks in a relational database that serves concurrent connections cannot be guaranteed to never occur.

On Tue, 15 Dec 2020, 21:53 Antony Stone, <Antony.Stone@mariadb.open.source.it> wrote:
Hi.

I hope this is a reasonable place to ask about Galera replication problems -
if not, please point me elsewhere and I'll happily ask others :)

I have four Debian 10 "Buster" servers installed with MariaDB 10.1.47 and
Galera 25.3.19-2.

I have other servers which write to these machines (there's no single
"master", since I don't want any particular client to depend on any single
master being available), and also read from them (in case it matters, those
machines are running Asterisk, and writing Call Event Logs and Call Detail
Records into MariaDB via ODBC).

My problem is the optimistic locking used by Galera when replicating between
nodes.  It results in Asterisk log file entries such as:

WARNING[20089]: res_odbc.c:460 in ast_odbc_print_errors: SQL Execute returned
an error: 40001: [ma-3.0.6][10.1.47-MariaDB-0+deb9u1]Deadlock found when
trying to get lock; try restarting transaction

Now, that's all very well for an application which can respond to such a
warning (personally I'd call it an error, when a write did not take place, but
that's a question for Asterisk, not MariaDB) and perform the retry, but
Asterisk does not do this.

Therefore I'd like to find some way to:

1. tell Galera to use pessimistic locking for replication if possible (I can
accept the performance penalty)

2. tell MariaDB to automatically retry the write when the error occurs
(although I can't think of any way that could be done, since I can't create a
transaction of any sort - the operation is entirely determined for me by
Asterisk)

3. find a High-Availability (which basically means no single point of failure)
front-end for the whole cluster of 4 MariaDB servers so that the problem does
not occur.


Questions:

1. How do other people deal with this problem?

2. Are any of my potential solutions above actually feasible?  (If so, how?)

3. Does anyone have any alternative ideas about how to connect an application
which doesn't understand retrying database writes (Asterisk) with a database
which doesn't guarantee to write the data you give it (MariaDB + Galera)?


Thanks,


Antony.

--
Schrödinger's rule of data integrity: the condition of any backup is unknown
until a restore is attempted.

                                                   Please reply to the list;
                                                         please *don't* CC me.

_______________________________________________
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