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.
Evan Gozali's Weblog 
Easy comment-uncomment with 1 character
This is commented.
This is not.
Got this from a friend who got this from a friend who got this from a friend who…