How to avoid the concurrent problem in back-end when select-then-update from MySQL

Question

I'm using Java and MySQL. And I have a table:

create table serial_number (
    id int not null primary key auto_increment, -- PK
    sn int, -- The serial number
    available tinyint   -- 0: unavailable; 1: available
);

Here is the scenario:

• SELECT id, sn FROM serial_number WHERE available = 1 LIMIT 1; -- assume the id is 1
• UPDATE serial_number SET available = 0 WHERE id = 1;
• Use the serial number (sn) for other business.

How to avoid concurrent problems?

Answer 1

You need to use pessimistic locking or optimistic locking.

1. Pessimistic

SELECT ... FOR UPDATE
UPDATE ... SET... WHERE

2. Optimistic

SELECT id, sn FROM serial_number WHERE available = 1 limit 1; -- assume the id is 1
UPDATE serial_number SET available = 0 WHERE id = 1 and available = 1;