Pessimistic locks, optimistic locks, row-level locks, table-level locks

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