What will happen in this case ?

If 2 concurrent users are using the same web application almost at the same time and both are inserting Data in a trans. table.

I am using the mysql_insert_id() to get the last id inserted to use it later on to save in another trans. table. (ref. table).

Suppose Last autoincrement value is 1002.

1st user inserts a row in the trans.table

1.$theSQL = "insert into...." ;
2.$resultSQL = mysql_query($theSQL, $linkdb);
3.$transid = mysql_insert_id() ;

if at a time gap of a fraction of a second another user inserts a row in the table just after point 2.

what will i get the transid for the 1st user?

1003.
or
1004.

if i get 1003, that is fine and safe.
But if i get 1004, my DB will be completely out of sync, with one table having a diff. trans.id then the other one.

Come on, put ur thinking caps on, and tell me what will be the result in concurrent users Insert SQL.

i am a bit concerned about this type of senario.

Thank you.

Brij.

    • [deleted]

    • Post #2

    mysql_insert_id is completely multi-user safe.
    It is based on the database connection, and you can only have one user per connection.

    Something to think about: what could possibly be the point of this function if it was not multi-user safe?

      Brij:

      To add up on vincent's post you can even have heap tables (I believe that is what they call them) that reside only in RAM. They are user visible only. That is you can create a table in a script with the same name simulataneously and it will NOT be shared across other users. Great for temporary buffers and getting around mySQL's limitations, specially on nested selects. It stays only in RAM so its very fast and gets dumped upon completion of the script (connection closed)

      Saludos
      Gerardo

        Thanks vincent,

        that was a relief. great if it works in my web application and i don't get a 'garbage' entry with inconsistencies by a mere mysql_insert_id() function.

          Agreed, i can have temp. table buffers but how would I at the end, dump data to the main Transaction table.

          in ur senario, two tables will have a trans. id of 1003 and when u insert both the data from 2 tables into a final permanent table, will i not get a Duplicate key error !!

          i think what vincent says that mysql_insert_id is multi-user safe, should be alright and continue with it.

          Thank you, anyway.

            Write a Reply...