-1

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?

  • Your question is much too vague. What exactly do you want to know? What is "for other business", what is a "concurrent problem" for you? Show and explain detailed a real, concrete issue which must be solved instead of such a meaningless description. – Jonas Metzler Jan 07 '23 at 11:15

1 Answers1

0

You need to use pessimistic locking or optimistic locking.

  1. Pessimistic
SELECT ... FOR UPDATE
UPDATE ... SET... WHERE
  1. 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;
mslowiak
  • 1,688
  • 12
  • 25