[Resolved] Need help with "lock tables"

ppowell

I have an application that runs that I want to be able to lock any table that a transaction will take place (insert, update, delete only). Because of the complexity of the application and for security purposes, I cannot reveal any more information than this.

I will be, for example, doing a deletion of rows on table a_assoc, then delete a row from table a. How do I do this when the deletions are in completely separate PHP 4.2+ class methods? How do I lock a table, do the deletion, exit the method, enter second method, lock another table, do another deletion and exit - cleanly?

I've had serious deadlocks and logjams trying to do this that I am thinking it's not worth it and let this application collide with multiuser "threads" that PHP does not even use, but please, someone help me.

Thank you
Phil

Roger_Ramjet

Well, a good read of the manual would show you that

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

Wrap your delete query in each class method within the neccessary lock table and unlock table statements, don't relie on the subsequent lock statement to do the implicit release lock. Have each method test for success and report that back to whatever you are using to call the methods. You are into using classes so create a class method that calls the other methods sequentially.

ppowell

Sorry but I did just that. Each class method is wrapped around subsequent "lock tables" statements, and I get a false return upon doing so while the action took place only part of the way before quitting without throwing any errors, warning, notices, nothing.

Phil

Roger_Ramjet

Don't follow you.

My point is that each delete Query must be preceded by a lock table statement and followed by unlock table, within each method: 3 sql statements in a row.

Now if individual queries are failing then that is a seperate issue. If the procedure is failing then that needs debugging. Without any code I'm just guessing.

ppowell

I wound up solving the problem with a bit more on the fundamental side of things and again proving that PHP 4+ and MySQL 4+ are not truly thread-safe methologically friendly.

My connection resource link is embedded into an object, as is everything else in my programming for now, since I'm doing OO PHP. I open up a connection object and then connect. Once connected I then have to perform my transaction (in this case, delete). Of course, before that I lock table blah_assoc to delete rows there. If I lock this table, nobody else can get this table but me, HOWEVER, I CANNOT lock nor obtain any OTHER table at the same time unless I unlock ALL tables.

The procedure to lock blah_assoc and delete records will be found in the $this->disassoc() method. At the end of the method I will, of course, unlock all tables, right? One huge problem: If I do that before disconnecting from the DB at end of class application scope, the entire site crashes and produces a weird Fatal Error "cannot unlock tables...". So I can only unlock tables if the class object instance is still running AND I have not disconnected from the DB.

So basically you have to change your entire architecture to accommodate a persistent lock-unlock methodology within your thread, also implying that interspersed throughout various class methods you have to handle the threaded connection semaphore logically to prevent deadlock.

Phil