Hi, > could there be a way by which the autonumber > (autoincrement) would automatically start from billno 2, rather than > starting from billno 3 when we delete the last record where billno = > 2.
No, unfortunately I don't know of a way (unless using MAX(..) or course). > I dont want to use a select statement to get max (billno) and then use > billno+1 when inserting the next record. and also i think this method > may lead to inconsistency in data when in a multiuser enviornment. It should be save, but in theory it could result in a deadlock. If you do this: <start transaction> (A) SELECT MAX(ID) FROM TEST; (B) INSERT INTO TEST VALUES(...); <commit> If two connections would do that at almost the same time, then the following could happen: - Connection 1 at (A) locks TEST for reading (a shared lock) - Connection 2 at (A) locks TEST for reading (a shared lock) - Connection 1 waits at (B) for Connection 2 to release the shared lock. - Connection 2 waits at (B) for Connection 1 to release the shared lock. > Deadlock. The database would throw an exception. To solve this, you could use: (A) SELECT MAX(ID) FROM TEST FOR UPDATE; Regards, Thomas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
