Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?
1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegro ups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>
|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegro ups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>
|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegrou ps.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>