Is mixing transactions and table locks unrealistic in MySQL 5.7?

Question

I am looking to mix table locks and transactions in an API. I have selected MySQL 5.7 as the storage engine, though I am not particularly committed to any specific RDBMS. I am interested in seeing whether my current design is suitable for MySQL before I go too deep into it.

In my design I have three tables:

• expenditure (id, amount, title)
• donation (id, amount, title)
• contribution (id, amount, expenditure_id, donation_id)

As you can see, this is a simple many:many between two tables, with contribution being the bridge table. I have set up constraints so the relationship is enforced.

The application is basically an accounting program for a charitable institution - an administrator creates donation entries (money coming in from donors) and expenditures (money going out for charitable purposes). From time to time the administrator will assign donations to expenditures, by creating entries in the bridge table. Large donations can be used on several expenditures, and large expenditures will consume several donations.

What I am interested in is how to create these bridge entries in MySQL. I think I would have a function like this pseudocode:

function addContributions(int expenditureId, array contributions)

So let's say I have an expenditure of 5000, and I have two donations (ids 1 and 2) with amounts (3000 and 2500 respectively). I would want contributions of 3000 (all of the first donation) and 2000 (some of the second donation). The contribution rows would look like this:

[
  {expenditure_id: 1, donation_id: 1, amount: 3000},
  {expenditure_id: 1, donation_id: 2, amount: 2000}
]

I have some related observations:

• I have some validation to do e.g. to make sure the donations are not attached elsewhere
• I want to be able to rollback in the case of an error
• I want my validations to hold true during writing these records i.e. protect against race conditions between making checks on the database and writing the records
• I think this means I want a write table lock on the central table

This is my rough sketch of the code (Node, but the language doesn't matter):

export default class ContributionWriter {

  addContributions(expenditureId, contributions) {

    this.startLock();
    try {
      this.startTransaction();
      this.addContributionsWork();
      this.commit();
    }
    catch (error) {
      this.rollback();
    }
    finally {
      this.endLock();
    }
  }

  addContributionsWork() {
    // Initial validation
    this.detectInvalidContributions();
    this.detectTooManyContributions();
    this.detectOverCommittedDonations();

    // Write contribs
    this.writeContributions();
  }

  /**
   * Write the contributions, validations passed OK
   */
  writeContributions() {
    // DELETE FROM contribution WHERE id_expenditure = x
    // Loop for all contributions:
    //   INSERT INTO contribution ...
  }

  /**
   * Prevent any writes to the table(s) during the lock
   */
  startLock() {
    // LOCK TABLES contribution
  }

  /**
   * Releases the locked table(s)
   */
  endLock() {
    // UNLOCK TABLES
  }

  /**
   * Runs a `SET autocommit = 0` to start the transaction
   */
  startTransaction() {

  }

  /**
   * Runs a ROLLBACK op to cancel any pending changes
   */
  rollback() {

  }

  commit() {

  }

}

The manual gives this advice:

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly

However it also says (on the same page):

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables

So, I think these things are incompatible. I want to use transactions in order to be able to rollback in the case of a problem, so the database is not left in an inconsistent state. I also want to use write locks, so that my validations remain correct until the table is unlocked, otherwise a race condition could happen that would overspend a donation (by virtue of another user assigning one of the same donations).

But if LOCK TABLES commits an open transaction, then could I have this situation (time flows downwards)?

User 1                  | User 2
------------------------|------------------------
lock tables (write)     |
start tx                |
validation checks       |
insert A                |
                        | lock tables (write)
                        | start tx
                        | validation checks
insert B                |
error                   |
rollback                |
unlock tables           |

I wonder what happens here is that the lock tables for User 2 does an implicit commit on User 1's transaction, the rollback for User 1 does not do a full rollback, since some commit has been issued.

Or does the lock tables for User 2 force a wait on the basis that another session has the table lock, and it pauses the operation until User 1 releases the lock?

Answer 1

Table locks are not really designed for concurrency control because these lock all but one user out of a table. You can use these in one-off situations, such as special maintenance tasks to ensure that no other processes interfere with your work.

I would add an outstanding amount field to both expenditures and contributions tables to indicate the amount not matched yet (yes, this is a denormalised field).

I would add insert / update / delete triggers to the bridge table and when you create / modify / delete a matching entry, then the triggers would update the outstanding amount fields by deducting / adding the matched value from the the field value. Since the update operation requires an exclusive lock on the record being updated, the triggers will effectively queue up the modifications to the outstanding amount fields. The trigger can enforce that the value cannot go below 0 and raise an error if it did.