maria-discuss team mailing list archive

[Jan Zankowski <jan.zankowski@xxxxxxxxx>]

Hello,

I'd like to use GET_LOCK() and RELEASE_LOCK() on MariaDB 10.2 to implement
basic distributed locking, to prevent simultaneous runs of a job unrelated
to the DB.

I see however in the MariaDB docs <https://mariadb.com/kb/en/get_lock/>
that: "Statements using the GET_LOCK function are not safe for
statement-based replication."

I also see in MySQL docs
<https://dev.mysql.com/doc/refman/5.7/en/replication-features-functions.html>
that this is likely only for statements such as "INSERT INTO mytable
VALUES(GET_LOCK(...))."

Full excerpt from MySQL docs:
*The following restriction applies to statement-based replication only, not
to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(),
and IS_USED_LOCK() functions that handle user-level locks are replicated
without the replica knowing the concurrency context on the source.
Therefore, these functions should not be used to insert into a source table
because the content on the replica would differ. For example, do not issue
a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)). These
functions are automatically replicated using row-based replication when
using MIXED mode, and generate a warning in STATEMENT mode.*

Could someone please confirm that:
(a) We will be safe using GET_LOCK() & RELEASE_LOCK() in MIXED mode.
(b) If we used STATEMENT mode, the only case of not being safe is when the
return value of GET_LOCK() etc. is directly involved in some other DB
operations (e.g. inserted into a table). In particular, the use case when
the locks are used only to prevent simultaneous runs of an external job
should be fine.

I strongly suspect this is not what "unsafe" means in this context, but not
being an expert on replication, what I'm really worried about is that our
replication would simply blow up or get totally out of sync if I used
GET_LOCK(). Please calm these fears if you can. :)

Thanks!
Jan