| Author |
Topic |
|
sfortner
Yak Posting Veteran
63 Posts |
 Posted - 2011-05-24 : 00:35:27
|
| Hi, I've inherited a Sage Timesheet database which we're trying to use to insert payroll time, so we have no control over the table layout and cannot change it. Still getting deadlocked somehow, 6 times today with not a lot of usage, in spite of using holdlock and updlock hints (per the 1222 trace in the sql logs). This is coming from a front end with a grid of users and several columns with paycodes and a checkbox for per diems. When the employee is assigned a particular paycode a textbox is activated in the grid so the user can enter their time for that particular paycode. Some users will have paycodes like Regular (hourly) and others will have Salary textboxes, but not both of these. I have it set up currently where it just loops through each employee and paycode to insert time. May need to pass employee and paycode/duration as a key/value pair that is maybe comma-separated or something to insert all time for an employee with one call to the sproc rather than calling it several times.The tsp_TimeEntries table has no pk or fk, no clustered index, and 3 unique and 3 non-unique clustered indexes. There's a unique index on the ID field. Could someone help us in setting this up so that we get unique entries and are not deadlocking? Is Sage Timesheet blocking this call and we're the blocked victim? The pertinent pieces of the table look like this:CREATE TABLE [dbo].[TSP_TIMEENTRIES]( [ID] [int] NULL, [ENTRY_DATE] [datetime] NULL, [START_TIME] [datetime] NULL, [STOP_TIME] [datetime] NULL, [DURATION] [int] NULL, [IDS_0] [int] NULL, -- employee [IDS_1] [int] NULL, -- company [IDS_2] [int] NULL, -- jobname [IDS_3] [int] NULL, -- phase [IDS_4] [int] NULL, -- job titles [IDS_5] [int] NULL, -- wage code (specific paycode for federal jobs and more specific pay codes for ids_9) [IDS_6] [int] NULL, -- not used [IDS_7] [int] NULL, -- not used [IDS_8] [int] NULL, -- not used [IDS_9] [int] NULL, -- pay type code (general pay codes like regular, holiday, salary) [HAS_NOTES] [int] NULL, [TIMER_RUNNING] [int] NULL, [NOSTARTSTOP] [int] NULL, [RATE] [float] NULL, [STATUS_0] [int] NULL, [STATUS_1] [int] NULL, [STATUS_2] [int] NULL, [STATUS_3] [int] NULL, [STATUS_4] [int] NULL, [STATUS_5] [int] NULL, [STATUS_6] [int] NULL, -- non-billable [STATUS_7] [int] NULL, -- lunch [STATUS_8] [int] NULL, [STATUS_9] [int] NULL, [STATUS_10] [int] NULL, [STATUS_11] [int] NULL, [TIMECARDENTRY] [int] NULL, [TIMERBASED] [int] NULL, [IMPORTSTAMP] [int] NULL, [ENTRYTYPE] [int] NULL, [AUDIT_ACTION] [int] NULL, [AUDIT_EMP] [int] NULL, [CHANGE_DATE] [datetime] NULL, [ORIG_ID] [int] NULL, [ENTRYFLAG] [int] NULL) ON [PRIMARY]and the contents of the sproc we're using looks like this (we definitely need to move the selects outside the transaction):-- this has the ability to insert several rows for a given pay code + rows for per diem, loads, miles, special travel and housingALTER PROCEDURE [dbo].[rjc_inserttimeentry] ( @Ccompany varchar(10), @Cempno varchar(10), @Entrydate datetime, @Cpaycode varchar(10), @Cjobcode varchar(20), @Nduration float, @NonBillable bit, @PerDiem bit, @nloads float, @nmiles float, @nstravel float, @lunch bit, @HousingDeduction smallmoney, @Note varchar(max), @Status int = null)ASBEGINSET NOCOUNT, XACT_ABORT ONBEGIN TRYBEGIN TRANSACTION-- set up, initialize variables, perform checks, etc.declare @InsertedRows table(id int)-- this is just for the insert for a given pay codeinsert into tsp_TimeEntries(ID, ENTRY_DATE, START_TIME, STOP_TIME, DURATION, IDS_0, IDS_1, IDS_2, IDS_3, IDS_4, IDS_5, IDS_9, status_10, status_11, IDS_6, IDS_7,IDS_8,has_notes,timer_running,nostartstop,status_0,status_1,status_2,status_3,status_4,status_5,status_6,status_7, status_8,status_9,change_date,orig_id,entryflag,audit_action,rate,timecardentry,timerbased,importstamp,entrytype,audit_emp)output inserted.ID into @InsertedRowsselectmax(id) + 1, @DTdtime, @DTstime, @DTstime, @calcdur, @Nids0, @Nids1, @Nids2, @Nids3, @Nids4, @Nids5, @Nids9, @Status, 3, 0, 0, 0,case when isnull(@note, '') <> '' then max(id) + 1 else 0 end, -- notes0, 1, 0, 0, 0, 0, 0, 0, @NonBillable, @lunch, 0, 0, @DTdtime, max(id) + 1, 0, 0, 0, 0, 0, 0, 0, 0from tsp_TimeEntries with (HOLDLOCK, UPDLOCK)select top 1 @TimeID = ID from @InsertedRows -- return thisIF (isnull(@note, '') <> '')BEGIN select @Nhasnotes = @TimeID insert into TSP_NOTES (ID, NOTE_TYPE, NOTES) values (@Nhasnotes,0,@note)END-- other calls to reset variables for per diem, loads, miles, special travel and housing-- other inserts for these if they exist, still using the same type of insert with holdlock and updlockCOMMIT TRANSACTIONselect @TimeIDEND TRYBEGIN CATCH Declare @ErrorMessage nvarchar(4000), @ErrorSeverity int Select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY() Raiserror(@ErrorMessage, @ErrorSeverity, 1) -- roll back any active/valid or uncommittable transactions If (XACT_STATE() <> 0) ROLLBACK TRANSACTIONEND CATCHSET NOCOUNT, XACT_ABORT OFFENDThe sql log entry for one of the deadlocked inserts is below:2011-05-23 13:47:15.74 spid14s deadlock-list2011-05-23 13:47:15.74 spid14s deadlock victim=processf910482011-05-23 13:47:15.74 spid14s process-list2011-05-23 13:47:15.74 spid14s process id=processf91048 taskpriority=0 logused=0 waitresource=KEY: 6:72057594540916736 (88003aabf98b) waittime=4171 ownerId=10242497 transactionname=user_transaction lasttranstarted=2011-05-23T13:47:11.417 XDES=0x12f0e7380 lockMode=RangeS-U schedulerid=4 kpid=2264 status=suspended spid=68 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-05-23T13:47:11.417 lastbatchcompleted=2011-05-23T13:47:11.417 clientapp=.Net SqlClient Data Provider hostname=<server> hostpid=8532 loginname=f9user isolationlevel=read committed (2) xactid=10242497 currentdb=6 lockTimeout=4294967295 clientoption1=673316896 clientoption2=1280562011-05-23 13:47:15.74 spid14s executionStack2011-05-23 13:47:15.74 spid14s frame procname=RJC_STS.dbo.rjc_inserttimeentry line=96 stmtstart=8134 stmtend=9828 sqlhandle=0x03000600e58a96542098c700de9e000001000000000000002011-05-23 13:47:15.74 spid14s insert into tsp_TimeEntries2011-05-23 13:47:15.74 spid14s (2011-05-23 13:47:15.74 spid14s ID, ENTRY_DATE, START_TIME, STOP_TIME, DURATION, IDS_0, IDS_1, IDS_2, IDS_3, IDS_4, IDS_5, IDS_9, status_10, status_11, IDS_6, IDS_7,2011-05-23 13:47:15.74 spid14s IDS_8,has_notes,timer_running,nostartstop,status_0,status_1,status_2,status_3,status_4,status_5,status_6,status_7, status_8,status_9,2011-05-23 13:47:15.74 spid14s change_date,orig_id,entryflag,audit_action,rate,timecardentry,timerbased,importstamp,entrytype,audit_emp2011-05-23 13:47:15.74 spid14s )2011-05-23 13:47:15.74 spid14s output inserted.ID into @InsertedRows2011-05-23 13:47:15.74 spid14s select2011-05-23 13:47:15.74 spid14s max(id) + 1, @DTdtime, @DTstime, @DTstime, @calcdur, @Nids0, @Nids1, @Nids2, @Nids3, @Nids4, @Nids5, @Nids9, @Status, 3, 0, 0, 0,2011-05-23 13:47:15.74 spid14s case when isnull(@note, '') <> '' then max(id) + 1 else 0 end, -- notes2011-05-23 13:47:15.74 spid14s 0, 1, 0, 0, 0, 0, 0, 0, @NonBillable, @lunch, 0, 0, @DTdtime, max(id) + 1, 0, 0, 0, 0, 0, 0, 0, 02011-05-23 13:47:15.74 spid14s from tsp_TimeEntries with (HOLDLOCK, UPDLOCK) 2011-05-23 13:47:15.74 spid14s inputbuf2011-05-23 13:47:15.74 spid14s Proc [Database Id = 6 Object Id = 1419152101] 2011-05-23 13:47:15.74 spid14s process id=processfe9828 taskpriority=0 logused=748 waitresource=KEY: 6:72057594540916736 (ffffffffffff) waittime=281 ownerId=10242512 transactionname=implicit_transaction lasttranstarted=2011-05-23T13:47:11.467 XDES=0x119586a90 lockMode=RangeI-N schedulerid=8 kpid=788 status=suspended spid=79 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-05-23T13:47:15.463 lastbatchcompleted=2011-05-23T13:47:15.463 clientapp=Sage TimeSheet hostname=<Sage server> hostpid=7264 loginname=f9user isolationlevel=read committed (2) xactid=10242512 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=1280582011-05-23 13:47:15.74 spid14s executionStack2011-05-23 13:47:15.74 spid14s frame procname=adhoc line=1 stmtstart=748 sqlhandle=0x02000000c2d8ac141cc1fc15634516650fae697c6419e9dd2011-05-23 13:47:15.74 spid14s INSERT INTO TSP_TIMEENTRIES (ID,ENTRY_DATE,START_TIME,STOP_TIME,DURATION,IDS_0,IDS_1,IDS_2,IDS_3,IDS_4,IDS_5,IDS_6,IDS_7,IDS_8,IDS_9,HAS_NOTES,TIMER_RUNNING,NOSTARTSTOP,RATE,STATUS_0,STATUS_1,STATUS_2,STATUS_3,STATUS_4,STATUS_5,STATUS_6,STATUS_7,STATUS_8,STATUS_9,STATUS_10,STATUS_11,TIMECARDENTRY,TIMERBASED,IMPORTSTAMP,ENTRYTYPE,AUDIT_ACTION,AUDIT_EMP,CHANGE_DATE,ORIG_ID,ENTRYFLAG) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40) 2011-05-23 13:47:15.74 spid14s frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000002011-05-23 13:47:15.74 spid14s unknown 2011-05-23 13:47:15.74 spid14s inputbuf2011-05-23 13:47:15.74 spid14s (@P1 int,@P2 datetime,@P3 datetime,@P4 datetime,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 int,@P15 int,@P16 int,@P17 int,@P18 int,@P19 float,@P20 int,@P21 int,@P22 int,@P23 int,@P24 int,@P25 int,@P26 int,@P27 int,@P28 int,@P29 int,@P30 int,@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 datetime,@P39 int,@P40 int)INSERT INTO TSP_TIMEENTRIES (ID,ENTRY_DATE,START_TIME,STOP_TIME,DURATION,IDS_0,IDS_1,IDS_2,IDS_3,IDS_4,IDS_5,IDS_6,IDS_7,IDS_8,IDS_9,HAS_NOTES,TIMER_RUNNING,NOSTARTSTOP,RATE,STATUS_0,STATUS_1,STATUS_2,STATUS_3,STATUS_4,STATUS_5,STATUS_6,STATUS_7,STATUS_8,STATUS_9,STATUS_10,STATUS_11,TIMECARDENTRY,TIMERBASED,IMPORTSTAMP,ENTRYTYPE,AUDIT_ACTION,AUDIT_EMP,CHANGE_DATE,ORIG_ID,ENTRYFLAG) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40) 2011-05-23 13:47:15.74 spid14s resource-list2011-05-23 13:47:15.74 spid14s keylock hobtid=72057594540916736 dbid=6 objectname=RJC_STS.dbo.TSP_TIMEENTRIES indexname=TSP_HR_ID id=lock84dc3600 mode=X associatedObjectId=720575945409167362011-05-23 13:47:15.74 spid14s owner-list2011-05-23 13:47:15.74 spid14s owner id=processfe9828 mode=X2011-05-23 13:47:15.74 spid14s waiter-list2011-05-23 13:47:15.74 spid14s waiter id=processf91048 mode=RangeS-U requestType=wait2011-05-23 13:47:15.74 spid14s keylock hobtid=72057594540916736 dbid=6 objectname=RJC_STS.dbo.TSP_TIMEENTRIES indexname=TSP_HR_ID id=lock15861ae80 mode=RangeS-U associatedObjectId=720575945409167362011-05-23 13:47:15.74 spid14s owner-list2011-05-23 13:47:15.74 spid14s owner id=processf91048 mode=RangeS-U2011-05-23 13:47:15.74 spid14s waiter-list2011-05-23 13:47:15.74 spid14s waiter id=processfe9828 mode=RangeI-N requestType=wait--Steve |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-05-25 : 06:27:59
|
| Your locking is most liklely due to looping through employees. Try to re-write your procedure so that it does a single insert. If I understand your procedure correctly you are passing in parameters e.g. @Ccompany , @Cempno , @Entrydate etc for each employee then doing a little calculations then inserting this record. Better to fill a temp table / use a view / UDF to have all the data then do a single insert. You say "has no pk or fk, no clustered index, and 3 unique and 3 non-unique clustered indexes" I'm guessing you mean 3 unique and 3 non-unique NON-clustered indexes. And do you mean unique constraint rather than "unique index" Why not just put a PK / unique clustered index on ID? Also you are inserting max(id) + 1 if there is no index on this this causes a table scan. If you change this to an identity column you won't need to bother getting the next id or risk locking the table.The main way to stop locking is to touch as little rows as possible as little times as possible, so update data in sets and have indexes so data can be found quickly. |
 |
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2011-05-25 : 09:01:19
|
| Thanks for the response - yes I did mean there's 6 NON-clustered indexes and a unique constraint accomplished by selecting the unique checkbox for 3 of the indexes. If you look at the sproc, yes we're using max(id) + 1 to get/hold the next id. We cannot update this table to use an identity field because it's being used by Sage Timesheet to insert records as well, and they apparently have some sort of custom procedure to ensure a unique ID in their code since that is what's showing up in the sql trace. They wrap their insert with a "set implicit_transactions on", perform the insert, then call "IF @@TRANCOUNT > 0 COMMIT TRAN" to commit it. We actually had a regular consultant in - Steve - and he used Adam Machanic's SQL Query Stress utility (http://sqlblog.com/blogs/adam_machanic/archive/2007/06/28/new-version-of-sqlquerystress-released.aspx) and it helped diagnose things a lot. Another consultant had 25 query statements in this sproc, some duplicates in setting/resetting variables, and some were dog-slow due to missing indexes and honestly even circular references. I had added the transaction wrapper and updlock/holdlock because this thing is inserting several rows. However... I had not pulled the select statements out of the body of the sproc and that apparently caused a lot of issues when it was taking so long to update. I knew a transaction should be short, but was in disbelief how long it was taking.The outcome of the analysis lead Steve to lean this sproc out and I even suggested creating a second version yesterday that does one thing and does it well with just a single insert and no transaction. The single version is very fast even under load, but the old original multi-insert version is being reworked still so that only the inserts are the only thing in the transaction wrapper (this is for another screen in the UI that lets users insert time, per diems, loads, miles, and housing with one click). The select statements are being tuned and so it should be much better off. The SQL Query Stress utility was invaluable in showing us that under load - when Sage Timesheet inserts are occurring at the same time as our inserts - that the old version of our sproc was locking up the system with the long transactions.--Steve |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-05-26 : 05:22:00
|
| Not sure but if the ID field is indexed, if not add an index on it - either clustered (my first choice) or non-clustered. I still think you should be doing these inserts in blocks rather than for individuals. One method is to create a copy of [TSP_TIMEENTRIES], e.g. CREATE TABLE [dbo].[TSP_TIMEENTRIES_Temp]( --etcChange stored procedure to insert into this temp table (you'll need to still get the max(id) from TSP_TIMEENTRIES). Then in batches or periodically insert everything in this table into TSP_TIMEENTRIES and truncate TSP_TIMEENTRIES_Temp. And do the same for TSP_NOTES.Also What size is the table? If it is large you could partition it. To partition it you'd need a clustered index on the table.Also - unless your lock hints have improved the performance I would not use them - the normal advice is to let SQL Server set its own locks - it is usually better at it. |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2011-05-26 : 08:32:14
|
| Yes, as stated in the (long!) OP the ID field is indexed, actually several times. There's no clustered index on it for some reason - don't know why. Several of the Timesheet db tables are heaps. The block inserts sound like trouble because the COTS Sage Timesheet app is still using this same database and we have absolutely no control over what it's doing and no control over how this table is set up. If we "periodically" inserted everything from the tsp_TimeEntries there's going to be some IDs used in the temp table in the main table from Sage Timesheet. The table only has 241k rows in it, but we can't touch it due to the Timesheet app. I think the single inserts are workable and we've tuned some of the poorly performing selects, or rewritten them with other tables/views. Performance is pretty good now even through the UI after it does other checks for what pay codes to make available to the user with textboxes. I know it's a lot of the database touches, but it is much more workable this way, and we don't hold the database for very long at all.--Steve |
|
|
|
|
|
|
Deadlocks on insert with holdlock, updlock