SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2013
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What happens if Simultaneous Insertion query with same table

    i want to know what happens if there is a Insertion query in two function running in parallel or if query is inside a foreach loop..

    > one more thing is my table has a AUTO INC field, so if two insertion query runs on same table at the same time , then what happens ? will it give error?
    how to avoid any errors and do dis successfully?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the database engine will never execute them at the same time

    one will go ahead, the other will wait until the one is finished

    by the way, query inside a foreach loop is inefficient, there's usually a better way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2013
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually im trying to do my task in parallel in order to increase speed...
    d sql part, i have put some code inside a function, [in between these quries come]...
    and im calling the function via foreach loop to do the work in parallel..

    and r u sure about it?
    bcause this table contains some pattern,
    >when finds a pattern, it first searches the table if that pattern already exist, only when its not present it runs the insertion query and inserts that pattern...

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by coolguy010 View Post
    >when finds a pattern, it first searches the table if that pattern already exist, only when its not present it runs the insertion query and inserts that pattern...
    so you're doing a SELECT and then an INSERT, right?

    that's two sql calls

    better to do just one sql call, and you improve performance by 100%

    do the INSERT, and let the database tell you when it fails due to duplicate key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2013
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ya i thought somwat similar but will it not cause any error when it encounters a duplicate key ???

    and secondly i am doin insert because if the pattern exists in the table then, it will that particular records pattern no and store it in a var, this info will be used in the later part of the code ..

    >and wat were u telling abt the foreach loop, is it ok considering the situation ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by coolguy010 View Post
    ... but will it not cause any error when it encounters a duplicate key ???
    yes, of course

    your application code would catch the error from the database, and generate a user-friendly error message exactly like the error message you currently produce when you do the SELECT and find the entry that you want to insert already exists
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2013
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and how can we achieve that wen coding ?
    im using php, mysql..
    u didnt tell about the foreach loop..

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by coolguy010 View Post
    and how can we achieve that wen coding ?
    i dunno, man, i don't do php


    Quote Originally Posted by coolguy010 View Post
    u didnt tell about the foreach loop..
    you should not do it if possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by coolguy010 View Post
    and how can we achieve that wen coding ?
    im using php, mysql..
    u didnt tell about the foreach loop..
    The place to start is with the SQL for the insert.

    After the call the first thing you need to test for is if the SQL failed with a duplicate key.

    Rearrange the way that the code works to do away with the inefficient foreach. If you do need a loop then move the SQL calls outside the loop so that the loop is only running on PHP variables that have already been loaded from the database call.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2013
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the foreach is not being used now.. there is only one foreach now, which takes the contents frm array,..
    however the sql is inside a few for loops, where i dnt think most of the loops can be avoided..
    anyway thanks a lot guys..


Bookmarks

Posting Permissions

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