Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    5

    Unanswered: Simultaneous Insterts

    I have a customer who says that he has three people using my php form to insert a new record in a mysql table at the same time. He says that every once in a while, the data never inserts. Is it possible that two insert queries are being issued simultaneously and bouncing one out? In general, what is supposed to happen when two insert queries are executed at the same time on the same table?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in general, one of them waits

    if inserts are not happening, look at your php code and beef up the error reporting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    5
    is this true even if the users are both the same? people are using the same form which connects to the db with the same username/pass? Or does the user not matter? Does the db go by resurce?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why would you allow people to share username/passwords?

    it doesn't matter, the inserts can happen only one at a time anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937
    in general, one of them waits
    Are you saying that MySQL cannot do parallel inserts in two different transactions?
    Why is the second one waiting?
    There shouldn't be any locks created by an INSERT in another transaction (sounds like another of those MySQL gotchas...)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for myisam tables, the table is locked for each operation -- so one of them waits

    for innodb, mysql uses row level locking, so, yes, it's possible that two transactions can operate at the same time

    i think

    (i am not a DBA)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Posts
    50
    Quote Originally Posted by shammat
    Are you saying that MySQL cannot do parallel inserts in two different transactions?
    Why is the second one waiting?
    There shouldn't be any locks created by an INSERT in another transaction (sounds like another of those MySQL gotchas...)
    You can't do transactions with myisam tables, and this is the table type most people use, because it's faster than innodb.

    read this for more info on MySQL table locking:

    http://dev.mysql.com/doc/refman/5.0/...l-locking.html

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ASP-Hosting.ca
    You can't do transactions with myisam tables, and this is the table type most people use, because it's faster than innodb.
    Faster is relative. If you have a lot of concurrent updates, this doesn't sound faster.

    I always forget that MySQL has this flat-file mode

    Thanks for the link. I'll have a look at it.

  9. #9
    Join Date
    Jul 2006
    Posts
    5
    So basically, there is no way an entry could be totally disregarded right? Just to reiterate, I have a php form that connects to the mysql db and inserts a new row in a specific table. The table type is myisam. The form is using one username and password to connect as all forms do. If there are 3 people simultaneously inserting (submitting the form that inserts a new row), is it possible that one of those inserts just doesn’t happen? There is no error being sent back by mysql. All is normal, but the data isn’t there. Is this possible? Or does one connection wait for the current to finish, then also get executed? My tables are queried against between 100-1000 times a day. Should I convert to innoDB? what sort of performance loss will occur? Are there any code, sql command changes that need to happen if I do convert? Why does row level locking affect my case? I am not updating a row, I am inserting new ones. Thank you all for your input, I'm glad to have such experienced people discussing this issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •