I just received access to a MySQL Database where the ID is a float field (not autoIncrement). This database was first used with a C# Application that is not updated anymore.
I have to make a web app and I can't edit the type of field in the database neither make a new one.
So, how can I make "INSERT" query that will increment the ID and not create problem when multiple people is working in the same time ?
I tried to get the last id, increment by one, then insert into the table but it's not the best way if users are creating a record in the same time.
Thank you
CodePudding user response：
how can I make "INSERT" query that will increment the ID and not create problem when multiple people is working in the same time ?
You literally cannot make an INSERT query alone that will increment the ID and avoid race conditions. It has nothing to do with the data type of the column. The column could be INT and you would have the same race condition problem.
One solution is to use LOCK TABLES to block concurrent sessions from inserting rows. Then your session can read the current MAX() value in the table, increment it, INSERT a new row with the incremented value, and then UNLOCK TABLES as promptly as possible to allow the concurrent sessions to do their INSERTs.
In fact, this is exactly how MySQL's AUTO_INCREMENT works. Each table stores its own most recent auto-increment value. When you insert to a table with an auto-increment, the table is locked briefly, just long enough for your session to read the table's auto-inc value, increment it, store it back into the table's metadata, and also store that value in your session's thread data. Then it unlocks the table's auto-inc lock. This all happens very quickly. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html for more on this.
The difficult part is that you can't simulate this from SQL, because SQL naturally must obey transaction scope. The auto-inc mechanism built into InnoDB works outside of transaction scope, so concurrent sessions can read the latest incremented auto-inc value for the table even if the transaction that incremented it has not finished inserting that value and committing its transaction. This is good for allowing maximum concurrency, but you can't do that at the SQL level.
The closest you can do is the LOCK TABLES solution that I described, but this is rather clumsy because it ends up holding that lock a lot longer than the auto-inc lock typically lasts. This puts a limit on the throughput of concurrent inserts to your table. Is that too limiting for your workload? I can't say. Perhaps you have a modest rate of inserts to this table, and it won't be a problem.
Another solution is to use some other table that has an auto-increment or another type of unique id generator that is safe for concurrent sessions to share. But this would require all concurrent sessions to use the same mechanism as they INSERT rows.
CodePudding user response：
A possible solution could be the following, but it is risky and requires thorough testing of ALL applications using the table/database!
The steps to follow:
- rename the table (xxx_refactored or something)
- create a view using the original table and cast the ID column as FLOAT in the view, so the other application will see the data as FLOAT.
- create a new column or alter the existing one and add the AUTO_INCREMENT to it
- Eventually the legacy application will have to be updated to handle the column properly, so the view can be dropped
The view will be updatable, so the legacy application will still be able to insert and update the table through the view.
This won't work if:
- Data in the column is outside of the range of the chosen new datatype
- The column is referenced by a foreign key constraint from any other table
- Probably more :)
!!! TEST EVERYTHING BEFORE YOU DO IT IN PRODUCTION !!!
Probably a better option is to ask somebody to show you the code which maintains this field in the legacy application.