CODEWITHSUNDEEP
sqltransactionslockingsql-server-2000
GC.
GC.

Reputation: 1244

Locking hints for table with concurrent read and write access

I've seen a few similar questions but none that quite address this scenario.

I have an online assessment application that presents users with questions, one question per page, and records their answers. Users can navigate between questions, and their responses are automatically saved.

When navigating from one page (question Q1) to another (question Q2), the database has to:

The [RESPONSE] table is a point of contention with many users concurrently reading and writing. However, each user will only ever read and write to their own rows.

This leads me to think I could use (READUNCOMMITTED), and UPDATE with (NOLOCK) safely. My worry is, I don't want to have the situation where a user goes forward a page, and then jumps back and gets the old data before it has been updated. I can put the two operations inside a transaction, but if I'm using NOLOCK hints will that make any difference?

What locking strategy can I use to alleviate contention on this table?

We're currently on SQL2000.

Upvotes: 0

Views: 693

Answers (1)

Martin Smith
Martin Smith

Reputation: 453232

You should just need to use the rowlock hint and make sure that you have appropriate indexes such that the rows can be seeked to directly without needing to scan other rows belonging to different users.

Upvotes: 2

Related Questions