Query that discloses "Locks" on tables 1

[SantaMufasa]

I wrote a query for a popular non-MySQL database engine that displays WHO is locking WHICH table (from WHICH machine):

                                                    Machine name        
Locked Object        DB Login name    O/S Username  of Locker           
-------------------- ---------------- ------------- --------------------
TEST.T1              TEST (ACTIVE)    dhunt         TFMOBILE5331        
TEST.S_EMP           TEST (INACTIVE)  dhunt         TFMOBILE5331        
TEST.S_ITEM          TEST (INACTIVE)  dhunt         TFMOBILE5331        
************************************************************************

One of our MySQL-application-product managers requests a similar script for his MySQL environments. (He would at least like WHO is locking WHICH table.)

Can anyone either:[ul][li]suggest a MySQL script that achieves similar results, or[/li][li]put me out of my misery by confirming that I cannot produce such a query in MySQL. <grin>[/li][/ul]Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Does the fact that no one has responded to my question, in the week since my posting, mean that:[ul][li]This is not possible in MySQL?[/li][li]Resolution to this need may be possible in MySQL, but no one here knows how to do it?[/li][/ul]A meaningful response to either of the above possibilities will be helpful to me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[vacunita]

Don;t know if this is what you are looking for but have you tried something like:

Show open tables in '[red]dbname[/red]'

It should theoretically tell you which tables are in use or locked. Although it won't tell you who locked them.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[SantaMufasa]

Vacunita,

Thanks so much for responding. We are nearly there!

An important objective within my product manager's request is to know who is responsible for the lock. Have you any advice for me in that regard? Does MySQL have objects that I can query for that information?

Grateful for your help,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[vacunita]

?Unfortunately, I don't think that is possible in MYSQL.
You might want to read here: How to find out who is locking a table in MySQL


According to the Author, Its something that hasn't been implemented yet, probably because MYSQL doesn't store that information anywhere.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[SantaMufasa]

Thanks, Vacunita, for your help on this issue. Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[vacunita]

You're welcome, sorry I couldn't be more help.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.