September 22, 2008 at 3:19 am
Hi,
I need to lock some rows in sql server 2005 database table so that other users should not access those rows.
The scene is I will select some rows from sql table for updation. Any other user accessing the same set of rows should get 0 records while selection, but their query should not hang. Pls help as its very urgent and I have stucked up at this point.
Pls specify the alternative way like some query or so.
I would really be thankful.
Regards,
Shantaram
September 22, 2008 at 5:20 am
If you have a record locked, the READPAST table hint in another query will make the query skip the locked records.
This is typically a bad approach to record locking as it essentially makes it look like data is missing when it is really there. For example, if a user has the "Paul Smith" record locked, another user may come along and search for "Paul Smith". When they see the record is not there, the second user may decide to add the record either creating a user or (if you are lucky) getting a duplicate key error against a record they cannot see.
This is a very unorthodox approach and I would suggest you research alternatives carefully. Don't think this is a new idea - it is just one that many people have failed to make work.
September 22, 2008 at 6:09 am
Thanx a lot Michael for your quick reply.
Basically I am aware of the consequences. The requirement is not for any entry module. It is required for a Share trade reading program. The application would be for eg. when trade feed comes from stock exchange, my exclusive process lock those trades for some critical processing, meanwhile no one should select those records.
Hope your hint will pull me out from this.
Ragards,
Shantaram
September 22, 2008 at 7:48 am
You can specify a locking method in a select statement. Books Online has the specifics on how to go about doing this.
Another approach would be to put data into a "holding pen" table till the exclusive pre-processing was done, then load it into the main table(s) once it's available for more general consumption. Would probably have better performance, and it would absolutely guarantee isolation during that stage.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 24, 2008 at 4:57 am
IN MY APPLICATION, ONE PROCESS WILL SELECT THE RECORD FROM THE DATABASE. AFTER SELECTION, THOSE RECORDS WOULD BE UPDATED ON SOME CONDITIONS.
IN THIS SCENARIO I WANT TO SELECT THOSE RECORDS EXCLUSIVELY FOR THE PROCESS ONLY. THE LOCK SHOULD BE DONE WHILE SELECTION ONLY. SO THAT TILL THE REVERSE UPDATION
IS NOT DONE, NO OTHER PROCESS SHOULD BE ABLE TO SELECT THOSE RECORDS.
THE SOLUTION GIVEN BY Michael IS VERY GOOD BUT IT WILL WORK FOR UPDATION/INSERTION/DELETION. I WANT IT ON SELECTION ONLY.
AGAIN I NEED UR HELP.
September 24, 2008 at 8:05 am
As GSquared as said, you can specify the lock type with a locking hint in the select statement. Something like Select x from table with (updlock). This statement would take an update lock on any rows affected until your transaction/batch is completed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply