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
449 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
  • 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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 extract information from SQL Server on Database, Connection and Server properties

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now