MySQL Concurrency update

MySQL concurrency update common scenarios optimistic lock

select * from tab1 where id = ?;update tab1 set col1 = ? where id = ? and version = ?;

• Disadvantages
  • May fail to update under high concurrency, so need to retry (Select...,update ...) To improve the update success rate

Read lock MySQL 8.0 Reference Manual/.../Locking Reads

select * from tab1 where id = ? for update;update tab1 set col1 = ? where id = ?;

• Advantages
  • No retries required
• Disadvantages
  • May cause other related (include the For Update line content) concurrent operation to block

Condition update MySQL 8.0 Reference Manual/.../update Syntax

update tab1 set col1 = col1-1 where col1>1

• Advantages
  • Simple, one statement can be implemented
  • No retries required
• Disadvantages
  • Unable to get the value of the Col1 field before and after update
  • Applies only to simple where conditions

Asynchronous update

• Advantages
  • Reduce client wait Time
  • Increase concurrency on the service side
  • Convert concurrent operations into simpler synchronization operations
• Disadvantages
  • The service side and the client need to support asynchronous message notification because the result of an asynchronous operation can only be returned asynchronously
  • Message Queuing and asynchronous message notifications are required, and code implementations are more complex without the framework support available
  • Other logic that relies on updating results is not supported unless all logic is asynchronous

MySQL Concurrency update