In this post we will see write skew in action using Node.js, Postgres and Sequelize. We will use the example given in wikipedia where there are two accounts (tables) V1 and V2 with a single balance column. The tables are initialized to V1=V2=100. Either (or both) accounts can be debited subject to the constraint that V1+V2 should always be >=0. What happens when two transactions try to debit V1 and V2 with $200 concurrently? Transaction T1 tries to debit V1 and Transaction T2 tries to debit V2. Let’s find out.
const debit = async (t, account) => {
try {
const v1 = await V1.findOne({
transaction: t
});
const v2 = await V2.findOne({
transaction: t
});
const debit = 200;
const sufficient_balance = v1.balance + v2.balance >= debit;
if (sufficient_balance) {
if (account === 'v1') {
v1.balance -= debit;
await v1.save({ transaction: t });
} else if (account === 'v2') {
v2.balance -= debit;
await v2.save({ transaction: t });
}
await t.commit();
console.log(`transaction successfully committed! debited ${debit} from ${account}`);
} else {
console.log('you do not have sufficient balance to complete the transaction');
// we still need to call t.commit else the program will hang! try commenting out this line!
await t.commit();
}
} catch (e) {
console.error(e.name);
console.error(e.original?.code);
if (!is_rollback_failure(e)) {
await t.rollback();
} // dot NOT try to rollback a tx that is already rolled back or you will get an error! try it out!
throw e;
}
}
We can try running this code with the 3 isolation levels: READ COMMITTED, REPEATABLE READ and SERIALIZABLE. What output do you think you will get? Test your understanding with below:
> $ ./run.sh ⬡ 18.15.0 [±master ●●]
testing with READ COMMITTED
transaction successfully committed! debited 200 from v1
you do not have sufficient balance to complete the transaction
result of 1 transaction
fulfilled
result of 2 transaction
fulfilled
final balances
v1 = -100, v2 = 100
testing with REPEATABLE READ
transaction successfully committed! debited 200 from v1
transaction successfully committed! debited 200 from v2
result of 1 transaction
fulfilled
result of 2 transaction
fulfilled
final balances
v1 = -100, v2 = -100
testing with SERIALIZABLE
transaction successfully committed! debited 200 from v2
Committing transaction 6a62f5ec-93b1-4b54-af4c-80bc05b683db failed with error "could not serialize access due to read/write dependencies among transactions". We are killing its connection as it is now in an undetermined state.
SequelizeDatabaseError
40001
result of 1 transaction
rejected
result of 2 transaction
fulfilled
final balances
v1 = 100, v2 = -100
The REPEATBALE READ transaction ended up debiting both the accounts and the constraint V1+V2 >= 0 is violated. This is write skew in action. Side note: although the READ COMMITTED transaction gave correct behavior, its a matter of chance (race condition). If both transactions execute in lock-step both will end up debiting V1 and V2 as well. Only SERIALIZABLE level provides complete protection.
siddharth's space 