C# acquire lock from mysql database for critical section of code

Question

I'm using Asp.NET with a MySql database.

Application flow:

1. Order created in Woocommerce and sent to my app
2. My app translated the woo order object to an object to add to an external ERP system
3. Order created in external ERP system and we update a local database with that order info to know that the creation was successful

I have a critical section of code that creates an order on an external ERP resource. Multiple requests for the same order can be running at the same time because they are created from an external application (woocommerce) that I can't control. So the critical section of code must only allow one of the requests to enter at a time otherwise duplicate orders can be created.

Important note: the application is hosted on Elastic Beanstalk which has a load balancer so the application can scale across multiple servers, which makes a standard C# lock object not work.

I would like to create a lock that can be shared across multiple servers/application instances so that only one server can acquire the lock and enter the critical section of code at a time. I can't find how to do this using MySql and C# so if anyone has an example that would be great.

Below is how I'm doing my single instance thread safe locking. How can I convert this to be safe across multiple instances:

SalesOrder newOrder = new SalesOrder();         //the external order object
var databaseOrder = new SalesOrderEntity();     //local MySql database object

/*
 * Make this section thread safe so multiple threads can't try to create
 * orders at the same time 
 */
lock (orderLock)
{
    //check if the order is already locked or created.
    //wooOrder comes from external order creation application (WooCommerce)
    databaseOrder = GetSalesOrderMySqlDatabase(wooOrder.id.ToString(), originStore);

    if (databaseOrder.OrderNbr != null)
    {
        //the order is already created externally because it has an order number
        return 1;
    }
    if (databaseOrder.Locked)
    {
        //the order is currently locked and being created
        return 2;
    }

    //the order is not locked so lock it before we attempt to create externally
    databaseOrder.Locked = true;
    UpdateSalesOrderDatabase(databaseOrder);

    //Create a sales order in external system with the specified values
    newOrder = (SalesOrder) client.Put(orderToBeCreated);


    //Update the order in our own database so we know it's created in external ERP system
    UpdateExternalSalesOrderToDatabase(newOrder);

}

Let me know if further detail is required.

Answer 1

You can use MySQL's named advisory lock function GET_LOCK(name) for this.

This works outside of transaction scope, so you an commit or rollback database changes before you release your lock. Read more about it here: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock

You could also use some other dedicated kind of lock service. You can do this with a shared message queue service, for example. See https://softwareengineering.stackexchange.com/questions/127065/looking-for-a-distributed-locking-pattern

Answer 2

You need to use a MySQL DBMS transaction lock for this.

You don't show your DBMS queries directly, so I can't guess them. Still you need this sort of series of queries.

 START TRANSACTION;
 SELECT col, col, col FROM wooTable WHERE id = <<<wooOrderId>>> FOR UPDATE;

 /* do whatever you need to do */

 COMMIT;

If the same <<<wooOrderID>>> row gets hit with the same sequence of queries from another instance of your web server running on another ELB server, that one's SELECT ... FOR UPDATE query will wait until the first one does the commit.

Notice that intra-server multithreading and critical section locking is neither necessary nor sufficient to solve your problem. Why?

It's unnecessary because database connections are not thread safe in the first place.

It's insufficient because you want a database-level transaction for this, not a process-level lock.