Transaction + lock tables

[hokadiri]

Per mysql lock tables

LOCK TABLES is not transaction-safe

And

UNLOCK TABLES implicitly commits any active transaction

Recommended to do:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

But per golang transactions doc

Its recommended to:

End the transaction with (Commit, Rollback)

Hence the Mysql recommendation contradicts the golang recommendation. I haven't found proper guidance on how to use transactions with locking in golang.

Do you have guidance (with actual example) of how to do transactions with lock tables?

Would that guidance be don't use transactions but use a dedicated connections?

A doc on this would be very helpful.

FWIW, my use case a standard go webapp running as a pool behind a LB. Any webapp can write to the DB, I want to ensure consistency when writing.

Seems quite standard but I'm having a surprisingly hard time finding examples of this. Any help/guidance would be much appreciated.