Concurrency and Locking

GoArchive runs sequentially by design: two runs that would touch the same source table never run together, whatever their job names, root tables or destinations.

Locks without locking your tables #

A run holds two sets of named locks (GET_LOCK()) for its whole length, each on a dedicated session, and on a new tracking schema a third lock for a moment before them. They are names in the server’s lock registry, not table locks: GoArchive never runs LOCK TABLES, and a name locks no table or row, so the application’s reads and writes are not blocked by it. It only stops another GoArchive run.

1. The schema lock, on the destination, is held only while a run creates a new tracking schema. One run at a time creates the tables and the label; another run starting then waits for it, up to 60 seconds, finds the schema ready and goes on. If the lock is still held after 60 seconds, the run does not start (exit code 4) and names the connection holding it.

2. The job lock, on the destination, beside the tracking tables. A second run of the same job is refused: job "<name>" is already running: its advisory lock is held by another session (destination connection <id>).

3. A lock for every table of the job’s tree, root included, on the source, the one server every run that could conflict shares. A run whose tree shares a table with a running job is refused before it does anything:

   the source table <database>.<table> is in use by another GoArchive run (source connection
   <id>). Jobs whose tables overlap run one at a time; this run did nothing.
   

   This holds for every job type and across destinations: the same source tables archived to two destinations run one after the other, and so do two copy-only jobs reading the same tables.

A refused run exits with code 4 and does nothing, so a scheduler can simply try again later.

The unfinished-work guard #

The locks stop a live run. The guard protects a job that stopped with work in its log (a crash, an error, a stop during recovery) until its own rerun finishes that work. While both lock sets are held, a run is refused before it does anything when another job in the same tracking registry:

• is on the same source data;
• shares a table of its tree with this run’s;
• and is marked running, or holds unfinished roots (copied or deleting) in its log, whatever its status.

The same source data is:

• this server’s;
• that of a server whose transactions are in this server’s gtid_executed: a promoted replica, a clone, or a server this source replicates from;
• where the server cannot tell, every server’s database of the same name in the registry, counted as one: when gtid_mode is not ON, on a Group Replication member, and for shards provisioned from one another. Give each such source server its own job_schema (Database Connections ).

The refusal lists each job with its state and the tables it shares, and the way out: rerun that job so its recovery finishes its work. It exits 4 while every job it names is live (marked running, with a heartbeat within the last 60 seconds), and 1 otherwise: a stopped job’s work waits for that job’s rerun.

A job marked running with nothing in its log, whose process is gone, is cleared by hand (Tracking Table Maintenance ).

Finding a lock holder #

Every lock name is a prefix and a hash, so any table or job name fits. The server computes the same names, so you can find a holder from SQL:

-- the job lock of job_name in job_schema (run on the destination)
SELECT IS_USED_LOCK(CONCAT('goarchive:j:', LEFT(SHA2(CONCAT(LOWER('<job_schema>'), CHAR(0), LOWER('<job_name>')), 256), 32)));
-- the lock on database.table (run on the source)
SELECT IS_USED_LOCK(CONCAT('goarchive:t:', LEFT(SHA2(CONCAT(LOWER('<database>'), CHAR(0), LOWER('<table>')), 256), 32)));
-- the schema lock of a new tracking schema (run on the destination)
SELECT IS_USED_LOCK(CONCAT('goarchive:s:', LEFT(SHA2(CONCAT(LOWER('<job_schema>'), CHAR(0), LOWER('')), 256), 32)));

The result is the holding connection id, or NULL when the lock is free. A run also logs each lock with the job or table it stands for, at debug level. Locks need no privilege.

Heartbeat and lock sessions #

Every 30 seconds, and again before every transaction it commits, a run checks that its own sessions still hold both lock sets. If either is lost, the run stops and the transaction in flight rolls back: GoArchive never commits a copy, a delete or a log write without its locks.

If GoArchive’s process dies, the server frees its locks at once. If its host or network is lost, the server frees them within 5 minutes: a run started before then is refused at once, and the next start after that goes through.

A run needs at least two source connections and three destination connections (max_connections).

Limits of the guard #

• Not detected: a server that received the stopped job’s data only before a GTID migration, and a server outside a group that holds the group’s data. Re-bind the stopped job before another job runs on its tables.
• Not detected: a stopped job recorded on a replica of this source.
• One registry: a stopped job tracked in another job_schema does not block.
• One backend per connection: the locks live on one server, so a read/write-splitting proxy could send the locks to a replica while the deletes go to the primary. Each connection must reach one backend for the whole run.
• Group Replication in multi-primary mode: locks are local to a member, so runs connected to different members are not serialized.