Solved

Blocking

Posted on 2009-05-17
8
235 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 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