How to find out who is blocking

General discussion forum about all things SqlBase.
wilhelm
Germany
Posts: 26
Joined: 22 Mar 2017, 08:49
Location: Cologne

How to find out who is blocking

Post by wilhelm » 24 Apr 2018, 14:25

Hi everyone,

I was wondering if anybody could give me a hint on how to find out which process or which user is holding a lock and thereby blocking another transaction from finishing.
Is there something in the system catalog I could retrieve to find that out?
I found the locks tab in the 12.0 Sqlbase command center but that is nothing that the enduser could interpret.
Every input will be highly apreciated.
Thanks

Wilhelm

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

Re: How to find out who is blocking

Post by FRBhote » 25 Apr 2018, 04:54

I use:

SET SERVER SERVER1;
START AUDIT ABC OVERWRITE CATEGORY 8;

The file ABC.txt on the server should give you the information.

wilhelm
Germany
Posts: 26
Joined: 22 Mar 2017, 08:49
Location: Cologne

Re: How to find out who is blocking

Post by wilhelm » 25 Apr 2018, 14:20

Thanks, but as I see it, the log only shows who is blocked but not who is blocking.
I think there must be another way. The console shows locks when they occur and not when someone is colliding with them.

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

Re: How to find out who is blocking

Post by FRBhote » 26 Apr 2018, 05:22

I don't think that you have tried it. It very clearly says who the lock holder is. For example:

008,25-APR-18 15:03:03.82,LOCK HOLDER,VKBB,SYSADM,SUPPORT1,RL,300,0,0,Unknown,
DELETE FROM BILLS WHERE B_DATE>2018-01-31
008,25-APR-18 15:03:03.82,TIMEOUT,VKBB,SYSADM,SUPPORT1,RL,300,167760,1,Temp S-Lock,
SELECT MAX(KOT_DATE) FROM KOT

wilhelm
Germany
Posts: 26
Joined: 22 Mar 2017, 08:49
Location: Cologne

Re: How to find out who is blocking

Post by wilhelm » 26 Apr 2018, 06:20

Well, I did. We obviously have diffenrent server versions.
This is what I get:
000,25-APR-18 15:42:10.19,GLOBAL,ABC,8
008,25-APR-18 15:45:43.85,TIMEOUT,PIESDB,USER1,Station-l4,RL,10,0,0,Unknown,
select ...
008,25-APR-18 15:45:50.96,TIMEOUT,PIESDB,USER2,Station-l1,RL,10,0,0,Unknown,
select ...

There is absolutely no line stating LOCK HOLDER.
... and this shows, that this is not a properly supported interface to go with.
I think I cannot rely on an undocumented log file which changes with every version.

I have Sqlbase 12.0 mostly but also 9.01

EwaldP
Austria
Posts: 57
Joined: 07 Mar 2017, 08:00
Location: Austria

Re: How to find out who is blocking

Post by EwaldP » 26 Apr 2018, 06:43

Wilhelm, you're right. In SQLBase 12.0 the lock holder isn't displayed. Strange behaviour. SQLBase Command Center also should show locks (TAB Locks). But in my SQLBase 12 version, it doesn't work too. Please check if you have a better result.
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

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

Re: How to find out who is blocking

Post by FRBhote » 26 Apr 2018, 11:04

After trying 9 and a couple of versions of 11, I'm sticking to 8.5 - that's the only version that really works. And does not crash at the drop of a hat.

Log a ticket?

wilhelm
Germany
Posts: 26
Joined: 22 Mar 2017, 08:49
Location: Cologne

Re: How to find out who is blocking

Post by wilhelm » 26 Apr 2018, 13:20

@FRBote
Sorry to hear that. I have used 5.0, 7.5, 9.01 and 12.0 and never had any issues.
12.0 makes a real performance boost because it uses multiple threads.
I wasn't aware of problem with things missing in the audit.

@EwaldP
Thanks for the input. I can see the XLocks on the Lock Tab on my 12.0 console. As I am using RL mode, I can only see longer standing XLocks, but it works.
The Locks Tab is quite slow...

Cheers
Wilhelm

Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 29
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand

Re: How to find out who is blocking

Post by Steve Leighton » 27 Apr 2018, 05:06

Greetings @ FRBhote .
Would really appreciate you not make such fallacious comments as
After trying 9 and a couple of versions of 11, I'm sticking to 8.5 - that's the only version that really works. And does not crash at the drop of a hat
this does no-one any good when its not true ( or at least only your opinion )

We run SQLBase 12.1.1 with a 100Gb dB and 250 active users at once. No problems what-so-ever.
In fact performance is extreme.

Similar with 11.6 - although not multi-threading.

If you havent tried v12.1.1, then suggest you do so... unless you've coded it all wrong :(
Best wishes
Steve
Bankside Systems Ltd. ( New Zealand )
www.banksidesystems.co.uk

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

Re: How to find out who is blocking

Post by FRBhote » 27 Apr 2018, 05:30

No offence intended.

I did go up to 11.0.2, and finally had to convert to MS Sql server. The largest database is about 800 GB.

My bigger customers have about 300,000 new entries a day which need to be billed to about 30,000 clients, PDFs created, mailed and all finished in about 2 hours so that the staff can go home at a reasonable time. And that's just the beginning of the process.

Even the slightest hiccup gets me a flood of phone calls. Not fun. especially when I'm at the club having a drink to cool off.

Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 29
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand

Re: How to find out who is blocking

Post by Steve Leighton » 27 Apr 2018, 05:37

Then I think you neeed to push for a massive pay rise . Drinks can't be cheap at the club.
Commiserations for having to downgrade to SQLServ** , but I guess someone has to . :(

p.s. at 800Gb , sounds like you should be supporting Oracle rather than MS
Last edited by Steve Leighton on 27 Apr 2018, 05:40, edited 1 time in total.
Reason: Added text
Best wishes
Steve
Bankside Systems Ltd. ( New Zealand )
www.banksidesystems.co.uk

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

Re: How to find out who is blocking

Post by FRBhote » 27 Apr 2018, 10:38

Conversion wasn't too tough, but since I have a couple of hundred customers still using SB 7.6 (don't laugh!) where ANSI outer joins are not supported, I have to do:

Set sOuterJoin = ' DP_ID(+) = DPAC_DP_ID '
If nBrand = DBV_BRAND_ODBC
Set sOuterJoin = ' DP_ID =* DPAC_DP_ID '

Return to “General Discussion”

Who is online

Users browsing this forum: Ccbot [Crawler] and 0 guests