#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593

    DBI Locking/Blocking


    I have a script that is run from a web page. I want to be sure that multiple scripts cannot access the database concurrently and cannot proceed until previously running script is complete. My thinking is to use table locking to accomplish both. If I Iock a table in one script and another starts, will that script be blocked until the table lock is cleared and will scripts queue up properly? The first thing the script does is make a DBI connection (MySQL). If that is successful, it then does a query and that is where I want it to block.

    Is my thinking correct and will a simple LOCK TABLE do the trick? TIA.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    776
    Rep Power
    495
    It really depends on your database, but, usually, when you lock a record in write mode, other processes trying to access the same record will wait for the lock to be freed. This is done by the database engine, not by the Perl script.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,873
    Rep Power
    1225
    If I Iock a table in one script and another starts, will that script be blocked until the table lock is cleared and will scripts queue up properly?
    You will need to explicitly code the logic in each of your scripts to accommodate the queuing otherwise, the query from the script which does don't own the lock will fail without retrying/queuing.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    Originally Posted by FishMonger
    You will need to explicitly code the logic in each of your scripts to accommodate the queuing otherwise, the query from the script which does don't own the lock will fail without retrying/queuing.
    Thanks, that is what I was looking for. So perl will error rather than wait for a lock to clear. I'll have to read up on how to get the blocking behavior. I assume that is buried somewhere in the DBI docs.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,221
    Rep Power
    1809
    The first thing the script does is make a DBI connection (MySQL). If that is successful, it then does a query and that is where I want it to block.
    Just to be clear, are you going to block simple read access, or are you blocking updates?

    If you are just querying, I wouldn't attempt blocking at all. If you are updating, you can use the transaction methods provided by DBI to request atomic changes (depends on your db engine as well). Even there, transactions appear best suited to wrap multiple related changes.

    Just always check the DBI return codes for errors on any operation.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    There are really 2 aspects involved somewhat independent. One is normal transaction processing. However, the other is to prevent 2 scripts running at the same time. My hope was to use table locking for both which would minimize rewriting the script. However, it sounds like I really need to bite the bullet and rewrite the script using semaphore then the transaction aspect will be built-in.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo