Solved

Blocking

Posted on 2009-05-17
8
234 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
[X]
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
  • 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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