One user at a time...

  • Hi all!

    Need to find a good place to read and understand.

    I have several 100 users on my DB.

    Some processes delete & recreate posts in a table.

    IF user two looks at the table, while user one has deleted them, they are of cause difficilt to see.

    So user two should first have access, when user one is finished..

    But how to, without dead locks?

    And where to read about it in general terms

    Best regards

    Edvard Korsbæk

  • Edvard Korsbæk (2/24/2015)


    Hi all!

    Need to find a good place to read and understand.

    I have several 100 users on my DB.

    Some processes delete & recreate posts in a table.

    IF user two looks at the table, while user one has deleted them, they are of cause difficilt to see.

    So user two should first have access, when user one is finished..

    But how to, without dead locks?

    And where to read about it in general terms

    Best regards

    Edvard Korsbæk

    Not quite sure what you are asking. Are you saying you want user 1 to lock the row when they view it until they either cancel or save?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Deadlocks typically occur when you have two processes accessing data in different orders. It could be UPDATEs, but SELECTs could cause issues at times.

    However if you have different users accessing the same table, there isn't a deadlock. There can be blocking, but that's expected. You don't necessarily want User Two to see data that is in the process of being changed by User One.

    Perhaps if you describe things better, and more carefully, we can help. Some code would be interesting as well. If this is just speculation/planning, then let us know. One thing I'd say is that your post isn't clear and it's hard to understand what you mean. Take a few minutes and show your post to someone in your company that doesn't know the situation. They might help you describe the problem better for us.

  • I have this stored procedure run first:

    PROCEDURE [dbo].[Ep_delete_Counterbalances]

    @institutionid INT

    AS

    BEGIN

    DELETE FROM CounterBalance WHERE timesum IS NULL AND Roster_ID in (select ID from dbo.dutyroster where institutionsid = @institutionid and dutyrosternorm = 0)

    END

    after that, this is run:

    [dbo].[Ep_Create_Counterbalances]

    @Start_Date_For_Totals_Date DATETIME ,

    @institutionid INT

    AS

    BEGIN

    INSERT INTO CounterBalance (Employee_id, CounterBalanceType_id, Counterbalance_date, Minutes, Duration, TimeAccount_ID,

    posetive_negative, Protected, Roster_ID, DutyRoster_ID)

    SELECT B.employeeid, C.CounterBalanceType_id, B.dato, A.minutes, A.duration, A.timeaccountid,

    C.posetive_negative, 0, B.DutyRosterId, B.id

    FROM timeaccountmovement A

    INNER JOIN dutyrostershift B ON B.id = A.ownerid

    INNER JOIN Counterbalance_map C ON C.TimeAccount_Id = A.timeaccountid

    WHERE A.ownertype = 1 AND A.ownerid = B.id AND B.dutyrosterid in (select ID from dbo.dutyroster where institutionsid = @institutionid and dutyrosternorm = 0) AND (B.ShiftType = 0 OR B.ShiftType = 1 OR B.shifttype = 4 OR B.shifttype = 5 OR B.ShiftType = 6 OR B.ShiftType = 15

    OR B.ShiftType = 16 OR B.ShiftType = 20 OR B.ShiftType = 25 OR B.ShiftType = 26 OR B.ShiftType = 32 OR B.ShiftType = 38 OR B.ShiftType = 39 OR B.ShiftType = 40 OR B.ShiftType = 41 OR B.ShiftType = 42 OR B.ShiftType = 43 OR B.ShiftType = 44 OR B.ShiftType = 45)

    AND B.dato >= @Start_Date_For_Totals_Date

    AND A.timeaccountid = C.TimeAccount_Id

    END

    After this, i have a big procedure, which SELECTS some SUM based upon records in COUNTERBALANCE

    If there is only one user of the DB, no probs at all.

    But if user one runs [dbo].[Ep_delete_Counterbalances] , and [dbo].[Ep_Create_Counterbalances] is 50% finished before user two, starts the same two procedures, the content of dbo.counterbalance is unpredictable for both user one aned for user two, who both SELECTS some SUM based upon the counterbalance table.,

    So, I neee to be sure, that the tables involved in the two procedures is locked,meanwhile theese three procedures is run (Delete, insert, select)

    Understandable?

    Best regards

    Edvard Korsbæk

  • So if you need the entire procedure to complete can you just wrap it in a transaction?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If you're running a SELECT prior to the UPDATE, you might want to put an UPDLOCK hint on the SELECT. That way you take out the lock you want when you want it instead of having to escalate from a shared lock to an exclusive lock. That should help with deadlocks. Also, since deadlocks are generally a performance problem, try tuning the queries so that they run faster.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Sean Lange (2/24/2015)


    So if you need the entire procedure to complete can you just wrap it in a transaction?

    Seems llike the way to do it. This is the newbie section, so its:

    Begin transaction;

    My three procedures

    Commit transaction

    Right?

  • Edvard Korsbæk (2/24/2015)


    Sean Lange (2/24/2015)


    So if you need the entire procedure to complete can you just wrap it in a transaction?

    Seems llike the way to do it. This is the newbie section, so its:

    Begin transaction;

    My three procedures

    Commit transaction

    Right?

    That assumes that none of your procedures have transactions. Also, as Grant suggested above an UPDLOCK might be appropriate. Without knowing more about the actual procedures it is difficult to ensure that what I say is correct.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply