Table to Lock Data

Question

Table to Lock Data

JKSQL

SSCrazy Eights

Points: 8481
More actions
August 21, 2008 at 3:11 pm

#192669

We are trying to create a table or something in SQL that will allow us to have up to 200 users hitting it with selects, inserts, and deletes. the way the program would work is the user first into the transaction locks it. The other users are then stuck in read only. The person with the lock leaves the record and the other users apps are going to prompt to see if they can get the next lock. This makes it so if one user is deleting data or adding it does not get stepped on by other users. So the table will probably only have 3-10 fields and probably around 2-5K in records throughout the day. Does anyone have any advice or seen a request like this before?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

SQL already handles most of this for you. You shouldn't try to reinvent what SQL is already better at than almost any custom design could be.

All you have to do is use oyu Transaction boundaries appropiately. If you are concernd about the potential inconsistencies of the faster isolation levels or their effective optimistic concerrency effects then switch to a higher isolation level.

To be absolutely sure, issue a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, though that usually causes more blocking than users want.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

For more on this and some really good explanations of how it all works and what options are avaialable to you, look in BOL (Book OnLine) under "Concurrency Control".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

The common approach in a client/server, or web based application is to use optimistic locking.

This article explains it well:

http://www.mssqltips.com/tip.asp?tip=1501

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 4

Thanks I appreciate the feedback. We used the isolation on another solution that worked well. We are currently using timestamp all over to make sure we are versioning correctly. I was just reading that timestamp may change in 2008. What problems will that cause?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

JKSQL (8/22/2008)
Thanks I appreciate the feedback. We used the isolation on another solution that worked well. We are currently using timestamp all over to make sure we are versioning correctly. I was just reading that timestamp may change in 2008. What problems will that cause?

Staring with SQL 2000 (i think) ROWVERSION was introduced as a synonym for the data type TIMESTAMP. In SQL 2008, ROWVERSION becomes the data type and TIMESTAMP is now the synonym, and further is deprecated. You shouldn't notice any chnages until SQL 2011(?) when TIMESTAMP may be either removed or radically changed.

For now, I would recommend just using ROWVERSION instead.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]