Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Blocking

Posted on 2009-05-17
8
230 Views
Last Modified: 2012-05-07
I'm evaluation performance manager and am getting email notifications of "stuff"
Before I act on this particular type of notification I wanted to run it past you here and find out...
Is it as scary as it sounds?
The app in question is run by our sister company that I look out for.
I am getting one of these notifications around once per hour but no-one seems to be complaining at the client end.

The relevant stuff is:
[Monitor Type]:  SqlServerBlockDetector
[Response Ruleset]:  Block Notification
  Wait Type:            Intent-Exclusive [0x0800]
  Wait Time:            00:00:10

I Googled the lock type but was hoping for a definition in plain English!
Intent exclusive (IX) locks are used to indicate the intention of a transaction to modify some resources lower in the hierarchy by placing Exclusive (X) locks on those individual resources.

Is this something I should be bringing to the attention of the admin of the comapny that runs the app or the vendor themselves? Is this normal DB behaviour and the locking is simply helping to ensure data integrity?
0
Comment
Question by:QPR
  • 4
  • 3
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24409076
These locks are normal DB behavior when data mofidications are occurring.  The question is...what statements are running inthe background when the blocking occurs.
0
 
LVL 29

Author Comment

by:QPR
ID: 24409095
the top statement is a select which apparently is blocking an update statement (2 different users)
The most recent email said the duration was 5 seconds. I'm assuming this means it took 5 seconds between the user submitting their update statement till the time the block was lifted and they could resume?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24411868
To explain more about the Lock escalations you have mentioned, kindly provide the Isolation level set for your database and then the queries involved.

Ideally UPDATE or DELETE statements will be having Exclusive locks as you mentioned above.

Hope this helps you on more detailed information about Locks in SQL Server.

http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Author Comment

by:QPR
ID: 24416193
UPDATE ar_invoice_treatment
      SET    trinv_active = 'N'
      FROM   ar_invoice ar
      WHERE  ar_invoice_treatment.seq_ar_invoice_id = ar.seq_ar_invoice_id
      AND    ar.seq_party_id = @seq_party_id
      AND    ar.allocated = 'Y'
      AND    ar_invoice_treatment.trinv_active = 'Y'

INSERT INTO ar_invoice_treatment (
            treat_det_id,
            seq_ar_invoice_id,
            trinv_date,
            trinv_actioned,
            trinv_active,
            insert_datetime,
            insert_user,
            insert_process)
                  SELECT      treat_det_id,
                                @seq_invoice_header_id,
                           dbo.dbf_date_add_bus_days(@invoice_due_date, td.treat_det_bus_days),
                           'N',
                           'Y',
                           GetDate(),
                           host_name(),
                           'ARPOSTTREAT'
                  FROM     ar_treatment_detail td,
                           ar_treatment_type tt,
                           nc_client nc
                  WHERE    td.treat_type_id = tt.treat_type_id
                  AND      nc.treat_type_id = tt.treat_type_id
                 AND      nc.seq_party_id = @seq_party_id

Where can I check the isolation level? This is a vendors backend and I have had no experience with the design of the DB or the application that uses the data
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24416206
almost certainly your isolation level is read committed, the default.

Make sure the fields in which you're searcing and joining on are indexed.
0
 
LVL 29

Author Comment

by:QPR
ID: 24416462
thanks, the design of the db is out of my control. It's a 3rd party applications backend.
I'm trying to work out whether I should be passing this alert (approx 6 times a day) to the admins over at this branch or just ignore it and put it down to DB behaviour.

As an aside... I was looking at a free tool called SQL Heartbeat that shows all the connected users, their status and their last command. Most of them sit at "idle" with the last statement being begin tran.
This sounds like a terrible way to write an app(??) and asking for trouble.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24417273
If its out of your hands, not much you can do really.  The tables need indexes if they don't already have them.  So, I would pass this alert to any admins that you can....put it in someone elses hands.  :)
0
 
LVL 29

Author Comment

by:QPR
ID: 24417457
did that and they have tried to justify it by saying it's the nature of the work that is happening at the time. Most recent locks have been 5 and 7 minutes which seems pretty unacceptable to me.
Oh well have passed it on and told them I will disable the alerts for this action as they are happy to put up with it.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question