3

In InnoDB MySQL storage engine, a stored procedure is being used to generate primary key values of the form tbaaa000000.

Key Generation

Thus, format of primary key is: For a fixed length say l, first two characters indicate table_name, next m are alphabets and rest (l-2-m) are numeric (only digits).

The stored procedure has been coded to manually increment the rightmost m+n chunk of characters using dynamic SQL. This is done by finding the greatest ID (say tbaaa999999 )present in the table and incrementing it (to tbaab000000). By dynamic SQL, I mean it has been used to build SQL statements dynamically at runtime by incorporating variables inside the query.

Form Submission

Let us suppose we are trying to generate this primary key for a table Table1. Also, we have another table (say Table2) which is referencing the primary key of Table1 (Table2 has a foreign key on Table1).

Now, a form is submitted to server using a PHP script which inserts the form data into Table1 and Table2. However, the primary key ID was generated using the stored procedure during the execution of query for Table1, but this ID which would be simultaneously be required even for the insert query of Table2 which will be unknown at the PHP script.

Also, there maybe multiple clients trying to access the page and thus the PHP script gets run for all users and so the generating stored procedure can possibly generate same primary key ID for multiple users.

My questions:

  1. Due to the concurrency of requests, how can I manage generation and insertion of only unique IDs in Table1?
  2. How can I get the generated primary key of Table1 back at the PHP script for insertion into Table2?

SO encourages me to include what I have tried so I have included my efforts. However, I do not want the scope of answers to be limited so please try to think a method before reading my trial.

Method I thought of:

We can call a procedure from PHP script which takes insertion data as arguments and generates the primary key ID of Table1 as well as inserts the data into both the tables. However, this would fail since the entire procedure can still generate same ID for concurrent requests.

I have also explored a try-catch method at PHP script end which would call a function for generation and returning of the ID to PHP script. This would then try to insert into Table1 which would fail if same ID already existed (maybe due to concurrent another insertion processing for another user) and throw a duplicate key exception and try generation again thus giving uniqueness. However, I am not sure about this method as this maybe inefficient and may cause performance question.

j809
  • 1,499
  • 1
  • 12
  • 22

1 Answers1

1

This is what transactions are for. The pseudo-code for what you need to do is:

  1. Start transaction.
  2. Get the highest ID in Table 1.
  3. Increment this.
  4. Store the new row in Table 1.
  5. Commit the transaction.

If another client tries to access Table 1 while this is happening, they will be blocked until the transaction is committed.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you. I explored this method, but need to clarify that for transactions to work, what do I have to mention in MySQL? Do I have to keep the transactions serializable? Something like [this](https://stackoverflow.com/questions/16041664/mysql-transactions-and-concurrent-inserts) ? – j809 Dec 24 '15 at 09:04
  • I think the default settings should handle most cases. The issue in that other question is unusual synchronization between different tables, but that's not an issue for you. – Barmar Dec 24 '15 at 09:10
  • Thank you for discussing! I will try to test the concurrency and mark this answer accepted after that :) – j809 Dec 24 '15 at 12:57