Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Activity manager- I have a process that is running 2 transaction and is being blocked by another process at awaiting command

Posted on 2008-06-17
5
Medium Priority
?
455 Views
Last Modified: 2008-06-18
SQL Activity manager- I have a process that is running 2 transaction and is being blocked by another process at awaiting command, i have a trigger running and i beleive it may be whats causing the issue as i when i disabled the trigger and the issue has stopped. the problem is is that i cannot recreate  the blocking issue consistently,  how can i get to the bottom of this?

Attached is the trigger code.
/****** Object:  Trigger [scheme].[mihaddcost_ins]    Script Date: 06/17/2008 22:25:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [scheme].[mihaddcost_ins]
 
ON [scheme].[opdetm]
 
AFTER INSERT
 
AS
 
--Trigger used to modify the driver field in opdetm with costs stored in stockm.
 
--The value entered depends on the currency of the customer.
 
--stockm.setup cost holds the sterling value
 
--stockm.shelf_life holds the euro value
 
 
 
    INSERT INTO [scheme].[opdetm_cost]
        
    SELECT i.order_no
          , i.order_line_no 
          , CASE WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND  s.shelf_life > 0
                              THEN s.shelf_life 
                              WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND  s.shelf_life = 0
                              THEN s.standard_cost
                              WHEN RTRIM(LTRIM(c.currency)) = '' AND s.setup_cost =0
                              THEN s.standard_cost
                              WHEN RTRIM(LTRIM(c.currency)) = '' AND s.setup_cost > 0
                              THEN s.setup_cost
                              ELSE 0
                          END
           * CASE WHEN i.order_line_status ='B'
							  THEN i.order_qty
							  WHEN i.allocated_qty>0
							  THEN i.allocated_qty
							  WHEN i.despatched_qty>0
							  THEN i.despatched_qty
							  ELSE i.order_qty
						  END
 
          , CASE RTRIM(LTRIM(c.currency)) 
                      WHEN 'EUR' THEN i.val/1.27
                      ELSE i.val END AS STGVAL
          , o.date_entered
          , i.line_type  
          , o.territory 
       FROM INSERTED i
       JOIN [scheme].[opheadm] o
         ON o.order_no = i.order_no
       JOIN [scheme].[slcustm] c
         ON c.customer = o.customer
       LEFT JOIN [scheme].[stockm] s
         ON s.[product] = i.product 
        AND s.[warehouse]= i.warehouse

Open in new window

0
Comment
Question by:meteorelec
[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
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:floook
ID: 21809258
Try modifying the original INSERT statement (the one outside the trigger) to use row locking instead of page locking. By default, SQL Server locks an entire page for inserts.

Example:

INSERT INTO tableName with (ROWLOCK) (col1, col2) VALUES (1, 2)

On SELECT statements, you can turn locking off to make sure your queries do not lock each other.

Example:

SELECT * FROM tableName with (NOLOCK)

This select query will pull data even if a row is being currently updated. You will have to evaluate the need for this though. Sometimes you will not care if you do not get the most current data, sometimes it may be crucial.

You can specify NOLOCK on joins as well. Example:

SELECT a.ID, b.Name FROM tableA with (NOLOCK)
JOIN tableB with (NOLOCK)
ON tableB.ProductID = tableA.ProductID

This query will not be blocked by inserts, updates, deletes locking pages in any of the tables the query pulls from. If you add NOLOCK to all joins in the trigger and modify the INSERT outside it to use ROWLOCK, you should be all right.

If you have other queries running prior or after the original INSERT, inspect them and any other triggers from this aspect as well.

See if this helps.
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21811064
So with out the (ROWLOCK) on the insert would it mean that it would lock the table out until the insert is complete?
0
 
LVL 2

Expert Comment

by:floook
ID: 21812309
Not the entire table. Just the page the record is inserted to. But since a page contains multiple records, other selects will fail (unless they use nolock) until the page lock is released.
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21812389
so nolock means
that you can select a record even is locked,
it also means that when you select it will not lock the table,

is that correct?
0
 
LVL 2

Accepted Solution

by:
floook earned 2000 total points
ID: 21812825
NOLOCK is the same as READUNCOMMITTED. Here's description from books online:

"READUNCOMMITTED
Specifies that dirty reads are allowed, which means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL."

"The SET TRANSACTION ISOLATION LEVEL controls the default transaction locking behavior for all Microsoft® SQL Server" SELECT statements issued by a connection."

So, if you are running multiple select queries or queries with joins, you would set transaction isolation level. Otherwise you can use NOLOCK.

This will make sure SELECTs are not locked by INSERTs, UPDATEs or DELETEs.

As I said before, you will have to evaluate how crucial it is to have the most recent data or if you can pull the uncommitted data.

Also, in the Activity Manager, you can rightlick the command column and get the details. You will see which command is being blocked. Then you can apply the above to it to resolve the issue.
SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED
 
SELECT * FROM ... etc.
 
SELECT * FROM
JOIN ...
ON ...
JOIN ...
ON

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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