sails: Race condition in update

Reopening #189, since it doesn’t appear to be resolved.

There is a race condition in update (in adapter.js) that I’ve run into a few times. The current update implementation works by:

The problem is that if other updates happen between steps 1 and 2, the data returned in step 3 could be inaccurate (either omitting/including records that were/were not affected).

Since this is a MySQL specific driver, there’s a pretty clever way of capturing the ids for updated records.

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 19 (10 by maintainers)

Most upvoted comments

I don’t think transactions alone would help; at least not with the default isolation level.

From the MySQL docs:

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.

leedm777 on Jan 26, 2016