how to detect locks?

[kittyyo]

Hi there,

My program accesses an InnoDB database. I sometimes have to lock a table for a quite long time (10 seconds or so) to update, where I also forbid reading the table with a LOCK TABLE ... WRITE.
When other users then try to access this table I don't want the program to be blocked for 10 seconds waiting for the lock to be released. I'm thinking of a nice little message box such as "please wait while another user performs critical updates" or something. But how can I detect if the table is locked?

I read the following thread,
thread436-520964,
but I don't want to give all my users the SUPER privilege so that they see all running threads using SHOW FULL PROCESSLIST. Anyway, this command can only show me if the table is locked WHILE the thread issues LOCK TABLE .. WRITE, can't it?

What could I do? Is it the only solution to create another table that contains
TABLENAME string,
LOCKED tinyint unsigned
where LOCKED>0 if the table has been locked?

Thanks a lot,
Anne

 

[Bastien]

code to see what the error returned is, if its locked show a message and try to refresh the page in 10-15 seconds. If its not locked, then allow the access to the table

Bastien

Cat, the other other white meat

 

[kittyyo]

I was thiking of the following situation:

Two users log on to the database 'DB'.

User A issues the command
"LOCK TABLES tablename WRITE;"
and then works on the database.
He will issue "UNLOCK TABLES tablename" ten minutes or so later.

In the meantime, user B also issues
"LOCK TABLES tablename WRITE;"
and his thread gets blocked for the time A needs to be finished locking the table.

How could I detect that and present user B a message box that the table is locked?

 

[Bastien]

check the error listing in the manual, it will show what the possible errors are...or simply test it...then code to handle that error

Bastien

Cat, the other other white meat