MySQL Forums
Forum List  »  Delphi

mjbcomp Posts: 2 Joined: Tue 13 Apr 2021 22:45 Contact: Contact mjbcomp Is it possible to find out which user has a ROWLOCK on a table?
Posted by: Michael Britt
Date: April 14, 2021 05:56PM

i am trying to determine which user has a record locked in 'edit' mode.

In Rad Studio (Delphi) before i try and change a ROW value i use this code:

try
tCustomer.Edit;
except on E: EDAError do
begin
if e.ErrorCode = 1205 then Showmessage('Record in Use');
end;

This part works flawlessly as the MYDAC TMyTable or TMyQuery returns the 1205 error code indicating the current record/row is locked by another user. But if that user has walked away from their desk to go to lunch or if they're on the phone i want the second user who needs to get access to the record to at least know WHICH user has the record locked.

My Tables use Pessemistic locking. My database uses READ COMMITTED for the isolation level and i set the innodb_lock_wait_timeout to 1 for these specific operations in my client session so they will immediately get acknowledgement that the record they are attemtping to EDIT is in use by another user.

I'm wondering if after determining the record is 'in-use' is there some other Server side stored procedure or sql command the client connection can run to determine which MySQL connection_ID or user has the record locked?


Is something like this possible?

I know you can query CONNECTION_ID() or CURRENT_USER() but that is for the current client. I need a way to get at which CONNECTION_id or User OR WINDOWS LOGIN ACCOUNT is is.

Thanks in advance.

Options: ReplyQuote


Subject
Written By
Posted
mjbcomp Posts: 2 Joined: Tue 13 Apr 2021 22:45 Contact: Contact mjbcomp Is it possible to find out which user has a ROWLOCK on a table?
April 14, 2021 05:56PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.