record locking-is this all automatic?

Hi I am using SQL 2000 and will be working on a database that will have a lot
of users. I was wondering there may be a case were I need to lock a record,
like when one user will be modifying its' data. I was wondering how to do
this? I am using stored procedures. I will probably need to lock the record
after a user does a select on a particular table. Also what are some of the
ways around this, would one be creating multiple duplicate tables so several
users can be modifying in a sense the same record at the same time?
Thanks.
--
Paul G
Software engineer.The common way is to create a Locks table. Then when you want to lock, for
example, a sales order, you would first check for a lock already there.
SELECT * FROM Locks
WHERE LockIdentifier = "SalesOrder4389"
and if you find a lock, then someone already is editing it. If you don't
find it, you can insert your own lock
INSERT INTO Locks (LockDate, LockedByUsername, LockedByFullname,
LockIdentifier, LockedAtMachineName)
VALUES (getdate(), 'paulg', 'Paul Ganonymous', 'SalesOrder4389', 'NERZHUL')
You lock other things by having different lock identifiers
e.g. "Invoice332", "Customer33", "Ticket 98", etc
The only downside is that if someone's PC crashes (or lose connection)
before they have a chance to clean up their lock, the lock will be dangling,
and you'll have to have an administrative screen to see locks and to remove
them. Also, whenever someone logs in, maybe clean up their locks then.
Alternativly, you can use the sp_getapplock/sp_releaseapplock built-in to
SQL Server. It works exactly like i described above, but it's built into SQL
Server, and SQL Sever will clean up any locks when a user disconnects (no
dangling locks)
To get a lock:
EXECUTE sp_getapplock 'SalesOrder4389', 'exclusive', 'Session', 0
To release a lock
EXECUTE sp_releaseapplock 'SalesOrder4389', 'Session'
With applocks, you could even go overboard and have
"Shared" lock if a user is only going to be looking at it (and others
can look too)
"Update" lock if a user is going to be changing something (others can
look, but nobody else can modify)
"Exclusive" lock means only i get it.
Important to note: "Application Locks" in SQL Server doesn't mean SQL Server
locks anything for you. You still have to check for locks by trying to get
an applock. If you don't get it, you have to write the code to not proceed
to viewing/editing.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.|||SQL Server books online covers transactions and locking.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.