Update lost: New changes to overwrite first, the development of three ways to solve
1, raise the transaction level to the highest level transaction_serializable
Both sides of the operation should raise the level; The query uses the shared lock; updates use the update lock; one query, one update, the shared lock and the update lock conflict; When both sides update the lock and a deadlock occurs, the program automatically rolls back one side of the operation to avoid the loss of the update
2, pessimistic lock (plus the table-level lock)
Party: Query statement plus for update; the other side: query statement plus for update; When an UPDATE statement is made, the other party cannot update the operation
3. Optimistic lock
UPDATE statement set version number, update data in specified version
Party: Update account set money=money-200,version=version+1 where id=1 and version=0;
The other side operates the same version number, the data cannot be updated
The other party: Update account set money=money+200,version=version+1 where id=1 and version=0;
If the update is more, the query is less, with pessimistic lock, conversely, optimistic lock
Table-level lock, where the non-primary key
Row-level locks, where a primary key is typically an ID
If you use a table-level lock, other customers will not be able to do query operations, so remember to use row-level locks in development