Need help - How to check table locked by which user ?

Discussion forum about all things SqlBase or SqlTalk
adamjuni

Need help - How to check table locked by which user ?

Post by adamjuni » 16 Feb 2012, 08:47

Hi,

Need help on this .

How do i check tables lock by which user ?

system details :

- red hat linux
- system Unify
- SQL
- Hospital Information System

Problem : Sometimes user will accidentally lock tables, how do i know which user locking which table.

Thank You

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 16 Feb 2012, 13:46

If you are using Sqlbase then use this in sqltalk :

START AUDIT AuditFileName OVERWRITE CATEGORY 8;

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 16 Feb 2012, 17:04

Hi Feroz,

can you give me an estimate how this will decrease performance?

We have:
- 40 GB SQLBase 9.0.1 latest PTF
- one TD 4.2 application running as windows service 7 days 24 hours and doing a lot lot of traffic
- one C# .NET 2010 application running as windows service 7 days 24 hours doing litle traffic
- another TD 4.2 application running as client application on 20 - 50 clients doing litle up to a lot of traffic each

I have the problem, that sometimes (actually on two days in February) the seems to be a lock by someone and I want to find out, which statement causes the lock.

Can I run the audit above without noticeable performance decrease?

Regards,
Rainer

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 17 Feb 2012, 06:02

I run it at my larger clients' places all the time and I don't think that there is any performance issue.

If you run a performance audit that does a lot of file writing and is a problem.

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 17 Feb 2012, 09:56

Thanks!

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 21 Feb 2012, 13:28

Hi Feroz & Mike,

this audit works great for timeouts, but I deadlocks are not written to the audit file (SB 11.0.2 latest PTF).
As I remember, this is a known problem?
Does this also occure in SQLBase 9 (because our customer has this version)?
And what can I do to go around this problem?

Regards,
Rainer

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 22 Feb 2012, 06:03

Are you sure you got a deadlock? My audit fiule shows:

Code: Select all

008,21-FEB-12 20:53:23.87,TIMEOUT,VSL11,NITIN1,Nitin,RL,30,23966329,1267847,X-Lock,
INSERT INTO SYSADM.SAUDA 
(S_DATE, ....
008,21-FEB-12 23:38:07.96,DEADLOCK,VSL11,MESHRAM,Ashok,RL,30,1461,2270377,S-Lock,
SELECT * FROM SYSADM.DEMAT  ....

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 22 Feb 2012, 13:37

Hi Feroz,

yes I'm absolutely sure, that I got a deadlock in SQLTalk on a database on the server where audit was on.

Which version of SQLBase do you have?
Mine was 11.0.2.5669.

Regards,
Rainer

ursamajor
Netherlands
Posts: 51
Joined: 23 Aug 2019, 11:10
Location: Sliedrecht, The Netherlands

Re: Need help - How to check table locked by which user ?

Post by ursamajor » 22 Feb 2012, 15:25

We developped a lockmonitor for that. It can show all locks, or the x-locks, for all or one database. It shows who, how long, which database, which group, first and last page.

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 23 Feb 2012, 07:13

Try createing a deadlock:

In Sqlttalk A:

CREATE TABLE foo (col1 INT);
INSERT INTO FOO VALUES (1);
CREATE TABLE bar (col1 INT);
INSERT INTO BAR VALUES (1);
COMMIT;
UPDATE foo SET col1 = 1; <--------- EXECUTE UPTO HERE
UPDATE bar SET col1 = 1;

In Sqltalk B:

UPDATE bar SET col1 = 1;
UPDATE foo SET col1 = 1;

Run both

In Sqltalk A execute the second statement and you should get:

UPDATE foo SET col1 = 1
^
Error: 01801 LKM DLK Application deadlock


Then see if it is in the audit file.

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 23 Feb 2012, 15:35

Hi Feroz,

I did nearly the same, except that I used two existing tables and that I executet in SQLTalk:

SQLTalk A: update recid set id = id + 1;
SQLTalk B: update recid2 set id = id + 1;
SQLTalk A: select * from recid2;
SQLTalk B: select * from recid;
^
Error: 01801 LKM DLK Application deadlock

But nothing was in the audit file. :-(((

But again the question: Which version of SQLBase/SQLTalk did you use? In my case it was 11.0.2.5669.

Regards,
Rainer

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 24 Feb 2012, 06:16

That's funny. I just tried it on SB 8.5 and got:
000,24-FEB-12 10:43:26.31,GLOBAL,ABC,8
008,24-FEB-12 10:44:03.18,LOCK HOLDER,ISLAND,SYSADM,frbctd,RL,15,2744,2,U-Lock,
UPDATE foo SET col1 = 1
008,24-FEB-12 10:44:03.18,LOCK HOLDER,ISLAND,SYSADM,frbctd,RL,15,5511,2,U-Lock,
UPDATE bar SET col1 = 1
008,24-FEB-12 10:44:03.18,DEADLOCK,ISLAND,SYSADM,frbctd,RL,15,2744,2,U-Lock,
UPDATE foo SET col1 = 1
999,24-FEB-12 10:44:24.79,ABC
Can I see your audit file? Did you get the first two u-locks?

I don't have 11.0.2 here - it's at my customer's place very far away, but I do get the deadlocks in the audit file.

Do you get it on SB 9 at least?

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 24 Feb 2012, 06:26

Perhaps Mike can move this thread to the SqlBase section where the response is much faster?

RainerE
Germany
Posts: 2214
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Need help - How to check table locked by which user ?

Post by RainerE » 24 Feb 2012, 11:14

Hi Feroz
FRBhote wrote:Can I see your audit file? Did you get the first two u-locks?

I don't have 11.0.2 here - it's at my customer's place very far away, but I do get the deadlocks in the audit file.

Do you get it on SB 9 at least?
I didn't get anything in the audit file in case of the deadlock. Not for SB9.0.1, not for SB11.0.2.

I started the audit in SQLTalk 1 with the following command:
start audit global AudDlTm to c:\buffer overwrite category 8;

After this I stared a second SQLTalk and reproduced a timeout, rolled back everything and reproduced a deadlock. I got both error messages in SQLTalk 2. I did this on ServerA with SQLTalk 9.0.1 against SB 9.0.1 (only SB 9.0.1 is installed on this server). And I did it on WorkstationB with SQLTalk 11.0.2 against SB 11.0.2 (Only SB 11.0.2 is installed here). Here the complete audit file of this (only containing the timeout):

Code: Select all

000,24-FEB-12 11:17:08.09,GLOBAL,AUDDLTM,8
008,24-FEB-12 11:21:53.89,LOCK HOLDER,OFCTSTLOC,SYSADM,PC11-0006_Eb,RL,5,0,0,Unknown,
update recid2 set id = id + 1
008,24-FEB-12 11:21:53.89,TIMEOUT,OFCTSTLOC,SYSADM,PC11-0006_Eb,RL,5,8883,2,S-Lock,
select * from recid2
999,24-FEB-12 11:22:15.85,AUDDLTM
Regards,
Rainer

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Need help - How to check table locked by which user ?

Post by FRBhote » 24 Feb 2012, 12:26

:?

Really puzzled. Maybe Mike can shed a little more light on it?

Return to “SqlBase General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests