[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

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

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
meteorelec
Asked:
meteorelec
  • 3
  • 2
1 Solution
 
floookCommented:
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
 
meteorelecAuthor Commented:
So with out the (ROWLOCK) on the insert would it mean that it would lock the table out until the insert is complete?
0
 
floookCommented:
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
 
meteorelecAuthor Commented:
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
 
floookCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now