Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Transaction Processing Issue

    I have SQL server 2000 database. When 2 users select appropriate entries in a table into a cursor, which is called through the procedure, I get this error message.

    RUN Time Error

    [ODB SQL Server Driver][SQL_Server 'Transaction (Process id nn) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.']


    Process id nn = sp_cursoropen;1

    Please help

    Thanks

    Sushma

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Transaction Processing Issue

    When 2 users select appropriate entries in a table into a cursor, which is called through the procedure, I get deadlocked processes...
    What is described may result from any one of many (or multiple) loosely interrelated issues (including: cursor type used, poor use of locking hints, conflicting dml statement orders, inefficient application of transaction isolation levels, etc.).

    To improve the probability of getting useful input, you may wish to consider posting the table schemas, and the code of your procs / cursors, involved (sample data to use to reproduce the issue would also assist others in providing useful feedback).

  3. #3
    Join Date
    Dec 2002
    Posts
    5
    Thanks for the response.


    The structure of the table and the code is following.

    -----------


    CREATE TABLE [dbo].[ReqCandidates] (
    [Vig_ID] [int] NOT NULL ,
    [Event_ID] [int] NOT NULL ,
    [Line_Number] [int] NOT NULL ,
    [Annex_Ln] [int] NOT NULL ,
    [UTC] [char] (6) NOT NULL ,
    [UIC] [char] (6) NOT NULL ,
    [ULN] [char] (15) NOT NULL ,
    [User_UTC] [smallint] NOT NULL ,
    [Master_Run_Id] [int] NOT NULL ,
    [Candidate_Id] [int] IDENTITY (1, 1) NOT NULL ,
    [Rqrd_Qty] [int] NULL ,
    [Rqrd_MFE_ID] [int] NULL ,
    [Rqrd_MFE_Description] [char] (100) NULL ,
    [Rqst_UTC] [char] (6) NULL ,
    [Beddown] [char] (50) NULL ,
    [Beddown_Cmd] [char] (50) NULL ,
    [Selected] [smallint] NULL ,
    [Qty_To_Allocate] [int] NULL ,
    [MFE_ID] [int] NULL ,
    [Description] [char] (100) NULL ,
    [Status] [char] (5) NULL ,
    [PMAI] [int] NULL ,
    [Already_Allocated_PMAI] [int] NULL ,
    [Primary_Secondary] [smallint] NULL ,
    [Unit_Name] [char] (50) NULL ,
    [AEF] [int] NULL ,
    [Home_Base] [char] (50) NULL ,
    [Command] [char] (50) NULL ,
    [Region] [char] (50) NULL ,
    [AEF_ID] [char] (10) NULL ,
    [Select_Priority] [smallint] NOT NULL ,
    [Is_Blank] [bit] NOT NULL ,
    [Analyst_Adjustment] [bit] NOT NULL ,
    [Component_Abbr] [char] (8) NULL ,
    [Date_Last_Deployed] [datetime] NULL ,
    [UTC_PAA] [int] NULL ,
    [AEF_Type] [char] (1) NULL ,
    [Dest_Geoloc] [char] (4) NULL ,
    [Qty_Avail] [int] NULL ,
    [READY] [decimal](1, 0) NULL ,
    [PRIORITY] [smallint] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[ReqCandidates] WITH NOCHECK ADD
    CONSTRAINT [DF_ReqCandidates_UTC] DEFAULT ('') FOR [UTC],
    CONSTRAINT [DF_ReqCandidates_UIC] DEFAULT ('') FOR [UIC],
    CONSTRAINT [DF_ReqCandidates_ULN] DEFAULT ('') FOR [ULN],
    CONSTRAINT [DF_ReqCandidates_User_UTC] DEFAULT (0) FOR [User_UTC],
    CONSTRAINT [DF_ReqCandidates_Selected] DEFAULT (0) FOR [Selected],
    CONSTRAINT [DF_ReqCandidates_Qty_To_Allocate] DEFAULT (0) FOR [Qty_To_Allocate],
    CONSTRAINT [DF_ReqCandidates_MFE_ID] DEFAULT (0) FOR [MFE_ID],
    CONSTRAINT [DF_ReqCandidates_PMAI] DEFAULT (0) FOR [PMAI],
    CONSTRAINT [DF_ReqCandidates_Already_Allocated_PMAI] DEFAULT (0) FOR [Already_Allocated_PMAI],
    CONSTRAINT [DF_ReqCandidates_Primary_Secondary] DEFAULT (0) FOR [Primary_Secondary],
    CONSTRAINT [DF_ReqCandidates_AEF] DEFAULT (0) FOR [AEF],
    CONSTRAINT [DF_ReqCandidates_Select_Priority] DEFAULT (0) FOR [Select_Priority],
    CONSTRAINT [DF_ReqCandidates_Is_Blank] DEFAULT (0) FOR [Is_Blank],
    CONSTRAINT [DF_ReqCandidates_Analyst_Adjustment] DEFAULT (0) FOR [Analyst_Adjustment],
    CONSTRAINT [DF_ReqCandidates_Date_Last_Deployed] DEFAULT ('1/1/1980') FOR [Date_Last_Deployed],
    CONSTRAINT [DF_ReqCandidates_UTC_PAA] DEFAULT (0) FOR [UTC_PAA],
    CONSTRAINT [DF_ReqCandidates_Qty_Avail] DEFAULT (0) FOR [Qty_Avail],
    CONSTRAINT [DF_ReqCandidates_READY] DEFAULT (0) FOR [READY],
    CONSTRAINT [PK_ReqCandidates] PRIMARY KEY NONCLUSTERED
    (
    [Vig_ID],
    [Event_ID],
    [Line_Number],
    [Annex_Ln],
    [UTC],
    [UIC],
    [ULN],
    [Master_Run_Id]
    ) ON [PRIMARY]
    GO


    -------------- Procedure is


    -Select appropriate entries in ReqCandidates
    --Declare variables required by the cursor procedure
    Declare @Line INT
    Declare @PrevLine INT
    SET @PrevLine = 0
    Declare @Annex INT
    DECLARE @PrevAnnex INT
    SET @PrevAnnex = 0
    Declare @Allocated_Line_Annex INT
    DECLARE @Sel_Status varchar(5)
    DECLARE @UIC_Available INT
    Declare @Priority SMALLINT
    Declare @Cmd_Status varchar(3)
    Declare @Date_Depl Datetime
    Declare @Qty_Diff INT
    Declare @Rqrd_Qty INT
    Declare @Qty_to_Allocate INT
    Declare @PMAI INT
    Declare @Already INT
    Declare @UTC_PAA INT
    DECLARE @UTC varchar(8)
    DECLARE @UIC varchar(8)
    DECLARE @ULN varchar(16)
    DECLARE @Dest_Geoloc varchar(4)
    DECLARE @Can_ID INT
    DECLARE @RemovedBlank BIT

    --Declare cursor
    DECLARE Cur_Select CURSOR SCROLL DYNAMIC
    FOR SELECT Line_Number, Annex_Ln, Select_Priority, (CASE WHEN Beddown_Cmd = Command THEN 'In' ELSE 'Out' END) As Command_Status, Date_Last_Deployed,
    (CASE WHEN Qty_Avail >= Rqrd_Qty THEN Qty_Avail - Rqrd_Qty ELSE 100 + Rqrd_Qty - Qty_Avail END) As Qty_Differential,
    Rqrd_Qty, Qty_To_Allocate, PMAI, Already_Allocated_PMAI, UTC_PAA, UTC, UIC, ULN, Dest_Geoloc, Candidate_ID
    FROM ReqCandidates
    WHERE Master_Run_ID = @Master_Run_ID AND Vig_ID = @Vig_ID AND Event_ID = @Event_ID AND MFE_ID = 0 AND Select_Priority > 0
    ORDER BY Line_Number, Annex_Ln, Select_Priority, Command_Status, Date_Last_Deployed, Qty_Differential

    OPEN Cur_Select
    FETCH Cur_Select INTO @Line, @Annex, @Priority, @Cmd_Status, @Date_Depl, @Qty_Diff, @Rqrd_Qty, @Qty_To_Allocate, @PMAI, @Already, @UTC_PAA, @UTC, @UIC, @ULN, @Dest_Geoloc, @Can_ID

    While @@Fetch_Status = 0
    BEGIN

    --Reset @Allocated_Line_Annex if on a new line number
    If @Line <> @PrevLine OR @Annex <> @PrevAnnex
    BEGIN
    SET @Allocated_Line_Annex = 0
    SET @RemovedBlank = 0
    SET @PrevLine = @Line
    SET @PrevAnnex = @Annex
    END

    --Determine whether the current line still has assets available
    --Required because the values in the cursor are not updated by the UPDATE statements below
    SET @Sel_Status = (SELECT Status FROM ReqCandidates WHERE Candidate_ID = @Can_ID)

    --If elements are currently available and the number already allocated against
    --the line/annex requirement has not been met, then consider
    IF @Sel_Status NOT LIKE 'P-%' AND @Allocated_Line_Annex < @Rqrd_Qty
    BEGIN

    BEGIN TRANSACTION

    --Delete the blank record, we won't need it (faster to insert blank this way rather then checking at end of routine)
    IF @RemovedBlank = 0 BEGIN
    DELETE FROM ReqCandidates WHERE Master_Run_ID = @Master_Run_ID AND Line_Number = @Line AND Annex_Ln = @Annex AND Is_Blank = 1
    SET @RemovedBlank = 1
    END

    --Update selected and qty_to_allocate properties
    SET @UIC_Available = (CASE WHEN @PMAI - @Already <= @UTC_PAA THEN @PMAI - @Already ELSE @UTC_PAA END)
    SET @Qty_To_Allocate = CASE WHEN @UIC_Available < @Rqrd_Qty - @Allocated_Line_Annex THEN @UIC_Available ELSE @Rqrd_Qty - @Allocated_Line_Annex END
    UPDATE ReqCandidates SET SELECTED = -1, Qty_To_Allocate = @Qty_To_Allocate WHERE Candidate_ID = @Can_ID


    --Take out this Unit/ULNs as a candidate for other requirements
    UPDATE ReqCandidates
    SET Status = 'P-' + Status
    WHERE Master_Run_Id = @Master_Run_ID AND Component_Abbr <> 'ANG-H' AND UIC = @UIC AND ULN = @ULN AND
    Candidate_Id <> @Can_Id AND MFE_ID = 0 AND Status NOT LIKE 'P-%' AND Selected = 0

    --Take out any ULNs whose unit has another ULN w/same asset deployed to different geoloc as a candidate for other requirements
    UPDATE ReqCandidates
    SET Status = 'P-' + Status
    WHERE Master_Run_Id = @Master_Run_ID AND Component_Abbr <> 'ANG-H' AND UIC = @UIC AND UTC = @UTC AND
    Dest_Geoloc <> @Dest_Geoloc AND
    Candidate_Id <> @Can_Id AND MFE_ID = 0 AND Status NOT LIKE 'P-%' AND Selected = 0

    --Take out any UTC/UICs whose asset limit has been reached as a candidate for other requirements
    IF @PMAI - @Already - @Qty_To_Allocate <= 0
    BEGIN
    UPDATE ReqCandidates
    SET Status = 'P-' + Status
    WHERE Master_Run_Id = @Master_Run_ID AND UIC = @UIC AND UTC = @UTC AND
    Candidate_Id <> @Can_Id AND MFE_ID = 0 AND Status NOT LIKE 'P-%' AND Selected = 0
    END

    COMMIT TRANSACTION
    SET @Allocated_Line_Annex = @Allocated_Line_Annex + @Qty_To_Allocate
    END

    --Next Record
    FETCH Cur_Select INTO @Line, @Annex, @Priority, @Cmd_Status, @Date_Depl, @Qty_Diff, @Rqrd_Qty, @Qty_To_Allocate, @PMAI, @Already, @UTC_PAA, @UTC, @UIC, @ULN, @Dest_Geoloc, @Can_ID
    END
    CLOSE Cur_Select
    DEALLOCATE Cur_Select
    GO


    Thanks

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    I will try to reproduce the issue when I get the chance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •