January 8, 2009 at 2:43 am
i'm making transaction base software using sql 2000 . In my program i want to stop insert,update and delete when another user modifying same record set at same time.Some time i want to lock table and some times selected columns.
I don't have much project work experience with sql lock.
So can anyone guide me to learn more about LOCK. Better to show me examples also.
Thanks all
January 8, 2009 at 4:33 am
Did you have a look at Books Online topic "locking" ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2009 at 4:53 am
sampathsoft (1/8/2009)
i'm making transaction base software using sql 2000 . In my program i want to stop insert,update and delete when another user modifying same record set at same time.
Is there something lacking with SQL's own locking mechanisms?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2009 at 5:01 am
I go through Books Online. But still no better idea.
There are no any sql mechanism for it. Want to do my own (in my sp)
Thanks both of you
January 8, 2009 at 5:08 am
Try looking up 'Concurrency Control' in the SQL2000 Books Online.
January 8, 2009 at 5:42 am
sampathsoft (1/8/2009)
...Want to do my own (in my sp)...
with regards to locking, in many cases this is not a good solution.
If you want to be ablosutely sure you don't update data that has already been updated by enyone else - so your starting data would nolonger be correct - you could use a sqlserver proprietary datatype called timestamp column, adding it to the where clause of every update statement you perform (and mytimestampcolumn = @oldtimestampvalue) and check if @@rowcount = 0 to raise an "update failed" error ! ( + rollback transaction ? )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2009 at 9:41 am
sampathsoft (1/8/2009)
There are no any sql mechanism for it.
Excuse me?
SQL absolutely has the ability to lock rows been modified. It's an essential part of a relational database, isolation, the requirement that one transaction cannot affect another.
Want to do my own (in my sp)
Why? Generally, the home-brewed locking techniques that I've seen people use cause massive concurrency problems. What are you trying to do that makes SQL's built-in locking not work for you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply