maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

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