How to have one Auto Incremented id for two separate tables in mysql? I have 2 different tables, each having its own id column.

I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later be handled together by another component in the application, and the other component must have them uniquely identified by the id.

I thought to have a table counter with next_id column, and have a before insert trigger to each of these tables, that will take the current id from counter, use it for the new row, and increase it.

My question is, is this synchronized? i.e. what will happen if 2 different applications, or threads in a single application, insert columns to any of these tables?

Do I have to lock the counter table during the trigger? If yes, will it be locked until the end of the entire transaction, or just until the trigger ends?

Is there a better solution for this problem?
If the two tables share ID space, surely then they should have a common parent table?

I would create a parent table and make the two other tables children of it with foreign keys. The parent has the autoincrement field, and you have to insert there first before inserting the child. Add a "type" column to the parent table so you know in which table to look for the child with the given ID.

The biggest advantage to this approach is that you don't need any triggers. No performance or synchronicity problems to worry about.

In PostgreSQL you would implement this using inheritance, but I don't think MySQL has this feature.