SQL Select with Write Lock

SQL transaction is usually used when we want to make database changes in a consistent state. When inserting/updating row in a transaction, a write lock will be held by the transaction so other transaction cannot do update at the same time (*). This makes sure the updated row is not changed from the execution of update to the end of transaction.
*) this is an oversimplification, please read the transaction manual about isolation level for more detailed explanation

However in some cases transaction is not enough to prevent race condition. A race condition is something that occurs when 2 concurrent execution tries to access and change a piece of data at the same time.

Let’s use an example with following database schema in MySQL

CREATE TABLE account {
  id int NOT NULL PRIMARY KEY;
  balance decimal(10,0) NOT NULL default 0;
} 

Consider the following piece of code in NodeJS with bookshelf.js ORM

function addBalance(id, amount){
  return bookshelf.transaction(function(t){
    return Account.where('id', id)
    .fetch({ transacting: t })
    .then(function(account){
      account.set('balance', account.get('balance') + amount);
      return account.save(null, { transacting: t })
    })
    .then(function(account){
      console.log('Balance: ', account.get('balance'));
    });
  });
}

Promise.map([1000, -1000], function(amount){
  return addBalance(1, amount);
});

If we run the code, we will get the following result

Balance: 1000
Balance: -1000

It happens because when both “addBalance” function executes the SELECT statement, the balance value is still 0 for both. This is definitely not the result we want. Even if we execute 2 functions in parallel, we want a safety measure to keep the balance in consistent state, so the read and update process must happen atomically.
This is where a Select Write Lock comes into play. For MySQL, we can read the documentation here (Locking Reads).

I won’t talk about “Lock in Shared Mode” in detail because I haven’t tested it thoroughly, so I will only show what happens if we use “Select … For Update” statement.

function addBalance(id, amount){
  return bookshelf.transaction(function(t){
    return Account.where('id', id)
    .query(function(qb){
      qb.forUpdate();  // this is the important part
    })
    .fetch({ transacting: t })
    .then(function(account){
      account.set('balance', account.get('balance') + amount);
      return account.save(null, { transacting: t })
    })
    .then(function(account){
      console.log('Balance: ', account.get('balance'));
    });
  });
}

When we run the code again, it should give a consistent result

Balance: 1000
Balance: 0

As the documentation stated, “Select … For Update” tries to acquire write lock for the selected row(s) just like an actual “Update” statement. So the second “addBalance” function cannot execute its select statement until the first transaction finishes. This feature is very useful for atomic read & update functionality.

However as an important note, this is only valid if both “addBalance” function are done in separate transaction. It will break if we try to call them in the same transaction in parallel. I will talk about it in later post.

Advertisements
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s