0

There is the table:

id type serial_no
1 apple 1
2 banana 1
3 banana 2
4 apple 2
5 water 1

I want the serial_no to be auto increment for every type

I tried

INSERT INTO MY_TBALE
(type, serial_no)
VALUES
(
apple, 
(SELECT (COALESCE(MAX('type'), 0)+1 FROM MY_TABLE T1 WHERE type=apple)
)

, but it seems not working on multi-threads system. I got some duplicate serial_no on same type.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
willer john
  • 11
  • 1
  • Look at implementing a partitioned row_number in a view. – Stu May 23 '22 at 16:15
  • Yes, you have discovered a [race condition](https://devopedia.org/race-condition-software). You can't do what you're describing without locking the table. Only one thread at a time can have exclusive access to the table, so it can read the MAX serial_no value and insert a new row without competing with other threads. – Bill Karwin May 23 '22 at 16:24
  • read this https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html – fonz May 25 '22 at 05:21

0 Answers0