February 24, 2015 at 4:59 am
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
February 24, 2015 at 7:53 am
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/
February 24, 2015 at 8:34 am
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.
February 24, 2015 at 10:12 am
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
February 24, 2015 at 10:51 am
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/
February 24, 2015 at 12:56 pm
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
February 24, 2015 at 2:18 pm
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?
February 24, 2015 at 2:46 pm
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