detecting Update Conflict

When a user try to update the record I use timestamp field (upsize_ts) to
figure out if data was modified by other user.
However I found out @.@.DBTS may cause problem in Multi user senario and may
not return correct Tiemstamp value as it is not the timestamp for the curren
t
operation but for the database is there any inexpensive solutin to resolve
this.
Thanks
Tanweer
CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
@.rowversion timestamp OUTPUT,
@.people_phone_id int,
@.offsysid varchar(5),
@.people_id int=Null,
@.sequence_id smallint=Null,
@.code_type varchar(5)=Null,
@.code_value varchar(50)=Null,
@.modify_operator varchar(15)=Null,
@.upsize_ts timestamp=Null
AS
SET NOCOUNT OFF
DECLARE @.errormsg varchar(1000)
DECLARE @.errcode int
DECLARE @.rowcount int
DECLARE @.modify_date datetime
SET @.modify_date = GetDate()
UPDATE [dbo].[PEOPLE_PHONE] SET
[people_id] = case when @.people_id is null then people_id else @.people_id
end,
[sequence_id] = case when @.sequence_id is null then sequence_id else
@.sequence_id end,
[code_type] = case when @.code_type is null then code_type else @.code_type
end,
[code_value] = case when @.code_value is null then code_value else
@.code_value end,
[modify_date] = case when @.modify_date is null then modify_date else
@.modify_date end,
[modify_operator] = case when @.modify_operator is null then modify_operator
else @.modify_operator end
WHERE
[people_phone_id] = @.people_phone_id
AND [offsysid] = @.offsysid
AND [upsize_ts] = @.rowversion
SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
--Use it to check the value of Timestamp column
--print 'Timestamp' + convert(varchar(50),@.rowversion)
select @.rowversion
IF(@.errcode = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
@.people_phone_id AND [offsysid] = @.offsysid)
BEGIN
RAISERROR('Row not updated because it was changed', 15, 1)
RETURN 50003
END
ELSE
BEGIN
RAISERROR('Row is gone', 15, 1)
RETURN 50004
END
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'
RETURN @.errcode
GOeasist of all..
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
ALL OPERATIONS
COMMIT TRAN
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||A doubt, why do you bother in this scnario. There is only one update and no
select and no transaction. You will never get an erronous update in this cas
e.
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||Don't use @.@.dbts, but rather retrieve the actual value for the updated row.
If more than one row is updated at a time, then you'd have to store the
values in a temporary table (e.g. a table variable) or use the OUTPUT clause
in SQL 2005.
ML
http://milambda.blogspot.com/|||I guess I have to go with ML syggestion there is no easy way out.
May be a wish for SQL 2005 Service Pack2
Thanks
Tanweer
"ML" wrote:

> Don't use @.@.dbts, but rather retrieve the actual value for the updated row
.
> If more than one row is updated at a time, then you'd have to store the
> values in a temporary table (e.g. a table variable) or use the OUTPUT clau
se
> in SQL 2005.
>
> ML
> --
> http://milambda.blogspot.com/|||are you using sql server 2005'
--
"Tanweer" wrote:
> I guess I have to go with ML syggestion there is no easy way out.
> May be a wish for SQL 2005 Service Pack2
> Thanks
> Tanweer
> "ML" wrote:
>

Detect Record Conflict:

In a table or 'shifts' for employees i'm trying to test a proposed new shift
for conflicts, the employee's shifts must have 12 hours between them,
This makes sense to me but, is it going to be efficiant considering the
table could have ten's of thousands of shifts.
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
My basic concern is the use of the criteria on two seperate fields: START &
END is it possible to use indexes to maintain performance?
Kind regards,
Graeme.You can definitely increase performance by having, clustered index on
operatorid = and non clustered indedx on EndTime and Start Time
USE BETWEEN -AND so that the = is also taken into consideration
Regards
R.D
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> In a table or 'shifts' for employees i'm trying to test a proposed new shi
ft
> for conflicts, the employee's shifts must have 12 hours between them,
> This makes sense to me but, is it going to be efficiant considering the
> table could have ten's of thousands of shifts.
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> My basic concern is the use of the criteria on two seperate fields: START
&
> END is it possible to use indexes to maintain performance?
>
> Kind regards,
> Graeme.
>
>|||Hi R.D.
thanks for your response.. does this make sense?
Assumeing I calculate 12h before the proposed shifts start as 'A' and
12 after the proposed shifts end as 'B'
Method 1:
if the DB looks for shifts with a START < B
and END > A [this is simple criteria but would it cause massive table
scaning to determine results, it woiuld also locate very long shifts (months
even) that conflicted]
it would have to check every shift that starts before B to see if the shift
ends after A would this be done using two (seperatoe) index ss [on two
differnt DB Fields, (START & END) and then find matchese.
Method 2:
I''m going to have to assume the shift can't be more than 12h (or say 24 to
be safe) and then use BETWEEN on START AND BETWEEN on END.
for instance:
... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
R.StartTime BETWEEN A-24 and B)....
Notice the 24 hours beeing added/subtracted to limit the window of matched
records. as I'm restricting the results by OperatorID i'm only going to
expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
CONFLICT, more than 1 conflicts should be very rare!
Is this sort of thing necessary or does the Query Optimizer do this
automaticly? somehow?
Thanks for you help so far,
Kind regards,
Graeme.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
> You can definitely increase performance by having, clustered index on
> operatorid = and non clustered indedx on EndTime and Start Time
> USE BETWEEN -AND so that the = is also taken into consideration
> Regards
> R.D
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Graeme Stow
Query optimiser generally does everything if you have indexes. However the
final selection can be made only by analysing the execution plans. When you
have index on that, It would not choose Table scan.
Try different combination and select the best one using execution plan.
Thumb rule is thst execution plan should avoid( unless you are selecting
everything) table scan, book marks, filters and should use Index s or
clustered s.
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R.D.
> thanks for your response.. does this make sense?
> Assumeing I calculate 12h before the proposed shifts start as 'A' and
> 12 after the proposed shifts end as 'B'
> Method 1:
> if the DB looks for shifts with a START < B
> and END > A [this is simple criteria but would it cause massive table
> scaning to determine results, it woiuld also locate very long shifts (mont
hs
> even) that conflicted]
>
> it would have to check every shift that starts before B to see if the shif
t
> ends after A would this be done using two (seperatoe) index ss [on two
> differnt DB Fields, (START & END) and then find matchese.
> Method 2:
> I''m going to have to assume the shift can't be more than 12h (or say 24 t
o
> be safe) and then use BETWEEN on START AND BETWEEN on END.
> for instance:
> .... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
> R.StartTime BETWEEN A-24 and B)....
> Notice the 24 hours beeing added/subtracted to limit the window of matched
> records. as I'm restricting the results by OperatorID i'm only going to
> expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
> CONFLICT, more than 1 conflicts should be very rare!
>
> Is this sort of thing necessary or does the Query Optimizer do this
> automaticly? somehow?
> Thanks for you help so far,
> Kind regards,
> Graeme.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
>
>|||Graeme Stow
I missed something in my first response. I think R.operatorid is not
unique. And If combination of ShiftID and operatorid is unique(should be)
try creating clustered primary index on this. And Non clustered index on
start and end dates.
Regards
R.D
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Graeme Stow
> Query optimiser generally does everything if you have indexes. However the
> final selection can be made only by analysing the execution plans. When yo
u
> have index on that, It would not choose Table scan.
> Try different combination and select the best one using execution plan.
> Thumb rule is thst execution plan should avoid( unless you are selecting
> everything) table scan, book marks, filters and should use Index s or
> clustered s.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Hi R,
After creating the following indexs:
create index tblShiftRecords_IX1
on tblShiftRecords (OperatorID, StartTime)
create index tblShiftRecords_IX2
on tblShiftRecords (EndTime)
Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
would it be better to have that as NON-CLUSTERED and have a more usefull
index as clustered like StartTime?
The folowing [testing] query
SELECT *
FROM tblshiftrecords
WHERE operatorid = 546 AND
StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh,
12, '20050516 17:00:00') AND
EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
36, '20050516 17:00:00')
Produces -
1)Index S - cost 0.003327 (98%)
2)Bookmark Lookup - 0.000070 (2%)
3)Filter - 0.000001 (0%)
It's looking better,
Cheers for your help.
Kind regards,
p.s. the query plan is ignoring IX2 on EndTime.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
> Graeme Stow
> I missed something in my first response. I think R.operatorid is not
> unique. And If combination of ShiftID and operatorid is unique(should be)
> try creating clustered primary index on this. And Non clustered index on
> start and end dates.
> Regards
> R.D
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>|||Graeme
Thats great. You can last do one thing.
have covering index. create individual non clustered indexes on operators
id, start time and end time and see you can eliminate book mark.
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R,
> After creating the following indexs:
> create index tblShiftRecords_IX1
> on tblShiftRecords (OperatorID, StartTime)
> create index tblShiftRecords_IX2
> on tblShiftRecords (EndTime)
>
> Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
> would it be better to have that as NON-CLUSTERED and have a more usefull
> index as clustered like StartTime?
> The folowing [testing] query
> SELECT *
> FROM tblshiftrecords
> WHERE operatorid = 546 AND
> StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh
,
> 12, '20050516 17:00:00') AND
> EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
> 36, '20050516 17:00:00')
> Produces -
> 1)Index S - cost 0.003327 (98%)
> 2)Bookmark Lookup - 0.000070 (2%)
> 3)Filter - 0.000001 (0%)
> It's looking better,
> Cheers for your help.
> Kind regards,
> p.s. the query plan is ignoring IX2 on EndTime.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
>
>