Here's the problem:
a survey item comes in, data entry person puts an order number on it which defines the order in which the piece is entered, then there is a survey id which is generated from the primary key on the table. Currently when the order number is created the developer was just selecting max(order number) and adding 1, but what happens when 2 people are entering surveys...the order number is not correct.
We need to lock the table and insert the maximum number of responses for the given batch while processing. The below code selects the maximum number from the response table and locks the table explicitly with table hints.
My question is can the following transaction block work with just setting the isolation level to serializable instead of using the table hints?
Also, another question i have is, is it a best practice to always put begin tran/end tran on all update/insert/delete statements?
SELECT x.surveyTemplateID, ISNULL(MAX(surveyResponseNumber),'0') AS 'MaxResponseNumber' INTO #surveyTemplateMaxResponseNumber FROM surveyResponse WITH (TABLOCKX, UPDLOCK, HOLDLOCK) RIGHT OUTER JOIN @PerResponseTemplateSequence x ON surveyResponse.surveyTemplateID = x.surveyTemplateID GROUP BY x.surveyTemplateID
UPDATE @PerResponseTemplateSequence SET MaxResponseNumber = mrn.MaxResponseNumber FROM @PerResponseTemplateSequence s INNER JOIN #surveyTemplateMaxResponseNumber mrn ON mrn.surveyTemplateID = s.surveyTemplateID
INSERT INTO surveyResponse (surveyResponseNumber, surveyTemplateID, surveyID, surveyTemplateTypeID) SELECT (MaxResponseNumber + ResponseCount) AS surveyResponseNumber, surveyTemplateID, MaxsurveyID, surveyTemplateTypeID FROM @PerResponseTemplateSequence
UPDATE #RecordsToProcess SET IssurveyResponseSet = 1 FROM #RecordsToProcess rtp INNER JOIN @PerResponseTemplateSequence s ON s.MaxsurveyID = rtp.InsertedsurveyID
asked Oct 16 '12 at 04:26 PM in Default
Last question first, yes, having an explicit begin and commit transaction wrapper around data modification statements is a best practice, so you're right to follow that.
As to the general issue, because you're doing the modification of the data in multiple steps, you need to hold locks for the duration of the transaction. Although, a table lock is pretty severe. Contention must be awful if you have lots of people modifying the system. You can rely on locking within the system rather than issuing locks yourself if you did the updates as a single statement.
And yes, I think serializable would work better, especially if you eliminate the table lock.
answered Oct 16 '12 at 04:52 PM
Grant Fritchey ♦♦
Just a point to make, its better to consider using Identity columns if you are dealing auto increment integer columns(in your case surveyResponseNumber), you can use SCOPE_IDENTITY() in the insert stored procedures for further processes using the inserted value, this is more accurate.
answered Oct 16 '12 at 05:02 PM