MySql unique id for several records

Question

I use one table withe some casual columns such as id, name, email, etc...also I'm inserting a variable numbers of records in each transaction, to be much efficient I need to have one unique id lets call it transaction id, that would be the same for each group of data which are inserted in one transaction, should be increment. What is the best approach for doing that? I was thought to use

select max(transaction_id) from users

and increment that value on server side, but that seams like old fashion solution.

Answer 1

Add new table with auto_increment column

You can create new table with auto_increment column. So you'll be able to generate unique integers in thread safe way. It'll work like this:

DB::insert_into_transaction_table()
transaction_id = DB::mysql_last_insert_id() ## this is integer value
for each record:
    DB::insert_into_table(transaction_id, ...other parameters...)

And you don't require mysql transactions for this.

Generate unique string on server side before inserting

You can generate unique id (for example GUID) on server side and use it for all records inserting. But your transaction_id field should be long enough to store values generated this way (some char(...) type). It'll work like this:

transaction_id = new_GUID() ## this is usually a string value
for each record:
    DB::insert_into_table(transaction_id, ...other parameters...)

Answer 2

You could have another table usergroups with an auto-incrementing primary key, you first insert a record there (maybe including some other useful information about the group). Then get the group's unique id generated during this last insert using mysql_insert_id(), and use that as the groupid for your inserts into the first table.

This way you're still using MySQL's auto-numbering which guarantees you a unique groupid. Doing select max(transaction_id) from users and incrementing this isn't safe, since it's non-atomic (another thread may have read the same max(transaction_id) before you've had a change to increment it, and will start inserting records with a conflicting groupid).