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.

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.

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.

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.

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.