Pessimistic locks, optimistic locks, exclusive locks, shared locks, table locks, row locks, deadlocks?

Pessimistic locking: Every time you go to retrieve data, you assume someone else will change it, so every time you retrieve data, you lock it, so that someone else will block it until it’s locked. This applies to scenarios where the number of characters is in the majority. Such as row locks, table locks, read locks, write locks, syncronized locks, etc. Select num from goods where id = 1 for update;

Optimistic lock: Every time I go to get the data, I think that others will not modify it, so I will not lock it. But when UPDATING, I will judge whether others have updated the data during this period, and add a version or timestamp to the table to achieve this. This method is applicable to the scenario where the number of reads is in the majority. Optimistic locking is suitable for multi-read applications to improve throughput.

Workflow: Obtain the current data version Update operation Version +1 Obtain the version number when submitting the update. Compare the version number when submitting the update with the version number obtained for the first time. If the version number is the same, the resource is considered to be the latest and can be updated; otherwise, roll back or throw an exception

Case: transaction a open, male tellers to perform a read operation, first remove the amount and the version number, to perform write operations, amount to 120 at this time, the version number is 1, the transaction has not yet been submitted two open transaction, female clerk to perform a read operation, first remove the amount and the version number, to perform write operations, the amount to 50, the version number to 1, and transaction have not been submitted

Now commit transaction 1, amount changed to 120, version changed to 1, commit transaction. Ideally, this should change to amount = 50 and version = 2࿰