Database consistency read && current read

Today, the Little Buddy asked a SQL question:

Update T set status=2 where ID in (select ID from t where Status=1)

This SQL, in the case of concurrency, will there be a problem?

Hypothesis: The following discussion, the transaction isolation level of the database is read_committed

In fact, this can be easily tested, and concluded that There is a problem of missing updates.

To understand the two concepts first:

1. Consistent reading

The current database product level has implemented multi-version consistency, that is, MVCC, then with the MVCC, the database has realized the effect of non-blocking reading and writing.
But in order to achieve the read_committed transaction isolation level, and the statement-level read consistency, the SELECT statement needs to build a version, and the database is built based on a version number.
For example, Oracle uses the SCN number, in order to build this version, the resulting read, called consistent read.

2. Current Read
The database is in the process of writing, such as update, because to verify constraint, conflicts, and so on, you need to read the current data, including the transaction without commit, which produces the current read.

Oracle Chinese and English explanations:

Consistency read: Consistent read

Currently read: current-read

And the above statement, there are two kinds of reading.

Select: A consistent read is performed,
Update: The current read is performed.

In the case of concurrency: The second update may be blocked because it is updated to the same row (other updates to the bank's transactions are not committed).
When another transaction commits, the second update will build the current read again, and the status will be updated from 2 again to 2.

How to avoid:

1. Change the statement:
Update T set status=2 where ID in (the Select ID from t where Status=1) and Status=1;

2. Adjust the isolation level of the transaction