x

Table locks/hints necessary or Just set isolation level?

Hi all,

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?

BEGIN TRANSACTION

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

COMMIT TRANSACTION
more ▼

asked Oct 16, 2012 at 04:26 PM in Default

k_t_schmidt gravatar image

k_t_schmidt
110 2 2 4

@ Mr. Fritchey, thanks for your response. I hope i'm understanding this right... If I change the whole transacation block to an isolation level of serialable, then the table hints are not necessary at all, correct? I was worried if I do serializable then the insert/update will not work. sorry - newbie at isolation levels.
Oct 16, 2012 at 05:12 PM k_t_schmidt
@ Mr. Davis, YES not so good design, but for now this is what we are dealing with until we can do a redesign.
Oct 16, 2012 at 05:24 PM k_t_schmidt
Serializable will scope the locks to the transaction so you won't have issues with being able to select top ID and insert a new one within the same transaction block.
Oct 16, 2012 at 05:42 PM Robert L Davis
Robert has already answered the follow up, but I'll reinforce. Yes, serializable will ensure that the read locks are retained until the completion of the transaction while using serializable. It does emphasize the need to make sure you have an explicit transaction. I would not rely on the implicit transaction to cover me in this regard.
Oct 16, 2012 at 05:46 PM Grant Fritchey ♦♦
thank you all for your help! I will educate my coworkers now. :)
Oct 16, 2012 at 06:59 PM k_t_schmidt
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Oct 16, 2012 at 04:52 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 16, 2012 at 05:02 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

And because you can use the OUTPUT clause, you can insert/update more than one row at a time.
Oct 16, 2012 at 05:04 PM Grant Fritchey ♦♦
Thanks, I missed it, you can really do many tricks with OUTPUT clause...
Oct 16, 2012 at 05:07 PM Cyborg
Agree with all of the comments above but want to add that this is a perilous way to handle assigning IDs. You will have extensive blocking and possibly high potential for deadlocks. Highly recommend changing the design to use identity columns or upgrade to SQL 2012 and use sequences.
Oct 16, 2012 at 05:22 PM Robert L Davis
Definite vote for Robert here. I answered the question instead of trying to answer the problem, which I think he does. You might want to change that to an answer Robert. I'd vote for it.
Oct 16, 2012 at 05:43 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x60

asked: Oct 16, 2012 at 04:26 PM

Seen: 921 times

Last Updated: Oct 18, 2012 at 08:24 PM