0

My code is as follows:

  • read max(id) from table A
  • insert new row with new_id = max(id)+1 in table A
  • insert multiple rows in another table B, with this new_id.

So if there are multiple users trying to do this operation at the same time, they can get the same max(id) and insert two rows with the same id. Can I use TRANSACTION_SERIALIZABLE to prevent this, or is there another way to do it?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Hakim
  • 11
  • 1
  • 7
  • Don't generate IDs this way, use a sequence or an auto-increment column instead. – Mark Rotteveel Mar 26 '21 at 17:04
  • I can't use auto-increment I need that id in the next query, it's like inserting a new student then inserting his grades in another table with his id – Hakim Mar 26 '21 at 17:10
  • You can, use the JDBC generated keys facility to retrieve the inserted id and use it for the next statement. See [How to get the insert ID in JDBC?](https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – Mark Rotteveel Mar 27 '21 at 07:27
  • will this work on a multi-client server without causing problems? and I still want to know how to close the table until the row is inserted – Hakim Mar 27 '21 at 10:42
  • Ids generated using identity columns or sequences are safe under concurrent access: they are outside of transaction control and will always emit a value one higher than the previously emitted value (even if that previous transactions was rolled back). – Mark Rotteveel Mar 27 '21 at 11:13
  • so if two transactions tried to get the max_id it will return two different values (t and t+1) ? thanks a lot, that's what I need – Hakim Mar 27 '21 at 12:39
  • If you use a sequence or identity column, yes. – Mark Rotteveel Mar 27 '21 at 12:49

0 Answers0