New user IDs - use MAX() or auto increment?

[gargoylemusic]

My question is fairly simple. There seem to be two ways of incrementing the 'ID' field of a new record. One is to find the maximum ID, add one, and use that new value as the ID. Or one can use an auto-incrementing field. Why would someone use one method versus another? When is auto-incrementing not appropriate?

[fenway]

UID fields should always be auto-increment.. MAX() is unreliable, because you don't have consistent reads in MySQL with MyISAM table without transactions, so you can't be sure that you're adding one to the "last" record.  Auto-incrementing is always appropriate for uids; sometime count=count+1 makes sense for other fields, but the same warning applies.

[pixy]

AUTO_INCREMENT is the best, as fenway said, for ids.

[gargoylemusic]

[quote author=fenway link=topic=100678.msg397727#msg397727 date=1152978386]
UID fields should always be auto-increment.. MAX() is unreliable, because you don't have consistent reads in MySQL with MyISAM table without transactions, so you can't be sure that you're adding one to the "last" record.  Auto-incrementing is always appropriate for uids; sometime count=count+1 makes sense for other fields, but the same warning applies.
[/quote]
Then should I use an InnoDB table (and then use transactions?) I mean... is there any advantage to that (why does't everybody use auto_increment?)

[fenway]

Everyone does use auto-increment... MAX() is meant for other things, and transactions come with a huge overhead.

[gargoylemusic]

When I do that, the only thing unique about the record would be the auto-incremented ID; does that mean I don't have to wrap it in a transaction? (If two people create accounts at the same time... MySQL won't assign the same auto-id?)

(pardon my ignorance... if you have any decent tutorials on this kind of thing, please point me to the url)

[fenway]

That's correct... auto-increment is per table, and is connection & thread safe, as is LAST_INSERT_ID().