Locking tables on read

Question

I have a requirement where a web service is called to generate the next number in a list.

There are multiple clients and I don't want to lock the whole table just the record for the client I am getting the next number for. Each client has their own numbering system.

However, I need the table to be locked on read of the particular record so that I can generate the new number, save it and return it.

I was thinking of loading the whole list into memory, making changes there and then simple using enqueue and dequeue to stack the requests but this is not client centric. Shouldn't be an issue at the end of the day as requests will be low.

We are using Entity Framework and LINQ for data access.

I guess what I'm asking is what approaches you might use for locking on read as the main question.

Is it safe to assume SQL server or do you need to support other backends? — Conrad Frix
– Conrad Frix, Commented Dec 6, 2011 at 3:18

competent_tech · Accepted Answer · 2011-12-06 03:11:31Z

Assuming you are using SQL Server, the way that we handle this is to execute the SQL query with the UPDLOCK table hint in the SELECT statement.

We usually use this in inserts where we are getting the next record number (not our database design):

INSERT INTO TableName (RecordNumber) SELECT ISNULL(MAX(RecordNumber, 0) + 1 FROM TableName WITH (UPDLOCK)

Collectives™ on Stack Overflow

Locking tables on read

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related