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

Question

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

Application flow:

Order created in Woocommerce and sent to my app
My app translated the woo order object to an object to add to an external ERP system
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.

Do you have just one server as your database server?Or are you load balancing the database server too? — Saman Gholami
– Saman Gholami, Commented Jan 4, 2018 at 17:24

Bill Karwin · Accepted Answer · 2018-01-04 17:28:13Z

3

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

answered Jan 4, 2018 at 17:28

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2018-01-04 17:25:07Z

1

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.

answered Jan 4, 2018 at 17:25

O. Jones

110k17 gold badges135 silver badges188 bronze badges

2 Comments

big_water Over a year ago

I tried using the C# TransactionScope() object for creating a Transaction in code but it didn't work. Is the DBMS transaction lock different than starting a transaction in code?

O. Jones Over a year ago

TransactionScope + MySQL === 0 . bugs.mysql.com/bug.php?id=70587

Saman Gholami · Accepted Answer · 2018-01-04 17:31:16Z

1

You should use Transaction, which is a unit of work in database. It's making your code not only atomic but also it'll be thread-safe. Here is a sample adopted from mysql official website

The code you need:

START TRANSACTION


COMMIT // if your transaction worked
ROLLBACK // in case of failure

Also I highly recommend you to read about Transaction isolation levels: Mysql Transaction Isolation Levels

If you use the Transaction as I wrote above, you have a lock on your table, which prevents other queries, e.g. select queries, to execute and they will be waiting for the transaction to end. It's called "Server blocking", in order to prevent that just read the link intensively.

answered Jan 4, 2018 at 17:31

Saman Gholami

3,5127 gold badges34 silver badges72 bronze badges

1 Comment

Bill Karwin Over a year ago

MySQL does optimistic locking. In other words, you don't get a lock on a table until you explicitly query it. Just using a transaction doesn't help. The OP would have to use LOCK TABLES early in the transaction to lock several resources atomically.

Alex Paven · Accepted Answer · 2018-01-04 17:28:21Z

0

I don't think there's any nice solution for this using a database, unless everything can be done neatly in a stored procedure like another answer suggested. For anything else I would look at a message queueing solution with multiple writers and a single reader.

answered Jan 4, 2018 at 17:28

Alex Paven

5,5492 gold badges24 silver badges35 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related