mysql - Which locking scheme and isolation level should one use for sequence number generation? -

i know general practice used in industry generating sequence numbers.

i.e. max table. increment , store back.

in order work, isolation level and/or locking scheme should used.

i thought serializable should work fine. prevents updates table. selection can still done. so, value updated same. how can avoid this?

thanks!

anything within transaction scope subject race conditions.

so sql query last used value, increment it, , store in new row means 2 concurrent clients fetch same value , try use it, resulting in duplicate key.

there few solutions this:

1. locking. each client sets exclusive lock on rows read if use select ... update (as @daniel vassallo describes)

2. use auto-increment. mechanism guarantees no race conditions, because allocation of new values happens without regard transaction scope. benefit, no 2 concurrent clients same value. means, though, rollback doesn't undo allocation of value. last_insert_id() function returns last auto-increment value allocated current session, if other concurrent clients generating values in same table or different tables.

3. use external solution. generate primary key values not using sql other system in application. you're responsible protecting against race conditions. instance use counting semaphore.

4. use pseudorandom, unique id. primary keys need unique, don't need monotonically increasing integers. people use uuid() function generate random 128-bit number that's virtually guaranteed not have duplicates. primary keys have use larger data type such char(36) or binary(16) , it's inconvenient write ad hoc queries.

   select * mytable id = '6ccd780c-baba-1026-9564-0040f4311e29';

you mention in comment "read negative things" using auto-increment. of course feature in language has do's , don'ts. doesn't mean shouldn't use features -- means should learn how use them properly.

can describe concerns or of negative things auto-increment? perhaps folks on thread can address them.