I think what the documentation is trying to say is that you cannot get VALUE of GET_LOCK(...) inside insert, update, etc. because in statement based replication that VALUE will be obtained both on the master and then again, on the replica itself. So it would probably create data discrepancy between master and slave.

INSERT INTO mytable VALUES(GET_LOCK(...))
with replication in STATEMENT mode, you can get eg. "1" inserted to your db on the master and "0" on the slave.

True for functions which are non-deterministic, while GET_LOCK is deterministic, we could say is not deterministic on the slave because slave could hold different locks so you can be sure what it'll return on the master but you can't be sure what it'll return on the slave AFTER it gets read from replication log.

a. It's of course safe to do in your case, it isn't even DB related as you're not storing the returned value in the DB so the replica can't get out of sync because of that.
b. correct

Best,
S.

Dnia 8 grudnia 2020 10:28 Jan Zankowski <jan.zankowski@gmail.com> napisał(a):

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 that: "Statements using the GET_LOCK function are not safe for statement-based replication."

I also see in MySQL docs 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

_______________________________________________