locking this query?

Question

locking this query?

SQLRep

SSCrazy

Points: 2052
More actions
February 21, 2005 at 2:58 pm

#163698

Hi all,
I am trying to use shared (or S lock) on this query.
select * from job_type
How can i do this with shared lock on on this query?

Thanks

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

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

SQL server will use shared lock for the above. Can you please describe what you are trying to achieve.The article below has some more information.

http://databasejournal.com/features/mssql/article.php/3289661

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 2

A select statment always gets a shared lock on the resource by default. In case you do not want to have this lock issued(if it affects other users from having an exclusive lock) then you need to give locking hints. But by default this has shared lock.

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 3

Hi,

What problems are you having with this select statement?

Are you having locking issues..generally?

Regards...Graeme

SQLRep SSCrazy Points: 2052 More actions · Answer 4

Hi All,

Thank you for replying, Actually i am trying to achieve is this. if one user log in to access this sp or other sp's which modifies the data at the same time if other user logs in and access the data b ut the second user cannot change anything in to that particular record it just stays read only for the other user. hopefully this makes sense?

Any ideas!

Thanks

philcart SSC-Forever Points: 47795 More actions · Answer 5

Ok, this is the fourth discussion thread all about the same thing,

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=161763

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=161765

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=162099#bm162479

If you didn't get the answer you wanted in those other threads, why not post to the original thread, instead of starting multiple threads all about the same thing?

If I understand you correctly, you want to hold a lock on the record for the entire time that the end-user may be editing it. All other users only see this record as read-only.

SQL Server doesn't handle locking this way. You could add a locking hint to your query, but that would lock the record from being read. You could use the "nolock" hint to allow dirty reads, but how do you determine if the record is being edited?

Best option would be to add an extra field to the table. When a user "edits" the record you update this field to indicate that it's locked. Then it's a simple matter of the front-end app checking that field before allowing other users to edit the record.

If this doesn't meet your needs then I suggest you post more details about the application you have and why you would need this. Why aren't SQL Servers internal locking mechanisms appropriate for the task?

--------------------
Colt 45 - the original point and click interface