"Christian Hassa" <ttch@newsgroup.nospam> wrote in message
news:u%23ZkMQ3lEHA.1356@TK2MSFTNGP09.phx.gbl...
> Hi!
>
> We are using rowversion fields for optimistic locking in a webservice
> scenario.
>
> When updating data we also want to retrieve the new rowversion of our
> updated rows in order to check for a lock violation in subsequent updates.
>
> We are searching for the most efficient solution for selecting the new
> rowversion of our updated rows, something like:
> UPDATE .....; SELECT @@DBTS
>
> The problem is that @@DBTS is server global so if another update statement
> happens in parallel we will receive the wrong rowversion.
> The problem seems to me similar to @@IDENTITY vs. SCOPE_IDENTITY().
>
> Our current solution is executing the following statements in a
serializable
> tx:
> UPDATE T... WHERE id=X; SELECT versionField FROM T WHERE id=X
>
> My question is, whether there is a better solution to that problem
(probably
> similar to the SCOPE_IDENTITY()) or if the separate SELECT statement is
the
> most efficient way to solve our problem. We are aiming to avoid
unnecessary
> SELECT statements, especially with high isolation levels.
>
> Thank you,
> Chris
>
>

"Christian Hassa" <ttch@newsgroup.nospam> wrote in message
news:%23xJjACAmEHA.2680@TK2MSFTNGP15.phx.gbl...
> Hi!
>
> NEWID() is creating a Guid, so I would rather need to create a
> Guid/uniqueidentifier column instead of a timestamp column.
> Also, I would need to manage the Guid value by myself all the time
> (INSERT/UPDATE) whereas rowversion would be managed by SQL server.
>
> Sure I can create my own infrastructure for versioning/locking using
> Guid/uniqueidentifier, but my impression is, that rowversion is exactly
> there for this reason, so why should I do it on my own? Isn't there any
> meaningful way how Microsoft intended the rowversion field to be used, or
> is it unusable by design?
>
> Chris
>
>
>> You can create a timestamp column and use NEWID() as the value.
>>

"Christian Hassa" <ttch@newsgroup.nospam> wrote in message
news:e5upEJCmEHA.2504@TK2MSFTNGP14.phx.gbl...
> Hi Dan,
>
> thanks for following up on this thread.
>
> As I outlined in my previous response to oj, I can figure out how to use
> Guids managed by the client to do optimistic locking (thanks for the code
> sample anyway).
>
> However, fields of type "rowversion" (currently synonymous to timestamp in
> SQL server but different from Guid/uniqueidentifier) are not managed by the
> client but by SQL server. Therefore the solution oj and you are proposing
> (and which I also figured out already) is neither what I am looking for nor
> is it utilizing SQL servers rowversion feature.
>
> This is a similar question like where to manage identities - especially int
> identities: on the server side (using autoinc ints) or on the client side
> (having to synchronize distributed ID generators on the client side somehow
> in case of int identities).
>
> So my question still remains: *if* I want to use the rowversion (synonym for
> timestamp) feature of Microsoft SQL server, where I define a column of type
> "rowversion" for a table, that will be managed then by SQL server: *what* is
> the suggested way to retrieve the updated timestamp after updating/inserting
> into a table?
>
> In my opinion a similar function, like Scope_Identity() provided for server
> generated identity values, should be available for server generated
> rowversion/timestamp values. I think it is quite inefficient if I have to
> issue a second SELECT statement (while keeping the updated record locked) in
> order to retrieve the newly generated timestamp. We are working on a 2TB
> database with several db-operations per second, we are simply not allowed to
> lock certain tables for selecting back our new rowversion. I would still say
> the rowversion feature is not efficiently usable if it turns out for
> performance/parallelization reasons I rather turn away from this feature in
> favor to Guids I manage on my own.
>
> br
> Chris
>
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> schrieb im Newsbeitrag
> news:OywOvzBmEHA.3520@TK2MSFTNGP11.phx.gbl...
> > To expand on oj's response, you can use optimistic concurrency while
> > avoiding the roundtrip by generating the row version value in your
> > application rather than Transact-SQL For example:
> >
> > CREATE PROC UpdataMyTable
> > @MyKey int,
> > @MyValue int,
> > @OldMyRowVersion uniqueidentifier,
> > @NewMyRowVersion uniqueidentifier
> > AS
> > SET NOCOUNT ON
> > UPDATE MyTable
> > SET
> > MyValue = @MyValue,
> > MyRowVersion = @NewMyRowVersion
> > WHERE
> > MyKey = @MyKey AND
> > MyRowVersion = @OldMyRowVersion
> > IF @@ROWCOUNT = 0
> > RAISERROR ('Data modified by another user', 16, 1)
> > GO
> >
> > Rowversion/timestamp/NEWID necessitates that the SQL-generated value be
> > returned back to the application via a SELECT or OUTPUT parameter. I
> > would hardly say this is unusable since it services may applications quite
> > well.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Christian Hassa" <ttch@newsgroup.nospam> wrote in message
> > news:%23xJjACAmEHA.2680@TK2MSFTNGP15.phx.gbl...
> >> Hi!
> >>
> >> NEWID() is creating a Guid, so I would rather need to create a
> >> Guid/uniqueidentifier column instead of a timestamp column.
> >> Also, I would need to manage the Guid value by myself all the time
> >> (INSERT/UPDATE) whereas rowversion would be managed by SQL server.
> >>
> >> Sure I can create my own infrastructure for versioning/locking using
> >> Guid/uniqueidentifier, but my impression is, that rowversion is exactly
> >> there for this reason, so why should I do it on my own? Isn't there any
> >> meaningful way how Microsoft intended the rowversion field to be used, or
> >> is it unusable by design?
> >>
> >> Chris
> >>
> >>
> >>> You can create a timestamp column and use NEWID() as the value.
> >>>
> >
> >
>
>

> You can create a timestamp column and use NEWID() as the value.
>
> "Christian Hassa" <ttch@newsgroup.nospam> wrote in message
> news:u%23ZkMQ3lEHA.1356@TK2MSFTNGP09.phx.gbl...
>> Hi!
>>
>> We are using rowversion fields for optimistic locking in a webservice
>> scenario.
>>
>> When updating data we also want to retrieve the new rowversion of our
>> updated rows in order to check for a lock violation in subsequent
>> updates.
>>
>> We are searching for the most efficient solution for selecting the new
>> rowversion of our updated rows, something like:
>> UPDATE .....; SELECT @@DBTS
>>
>> The problem is that @@DBTS is server global so if another update
>> statement
>> happens in parallel we will receive the wrong rowversion.
>> The problem seems to me similar to @@IDENTITY vs. SCOPE_IDENTITY().
>>
>> Our current solution is executing the following statements in a
> serializable
>> tx:
>> UPDATE T... WHERE id=X; SELECT versionField FROM T WHERE id=X
>>
>> My question is, whether there is a better solution to that problem
> (probably
>> similar to the SCOPE_IDENTITY()) or if the separate SELECT statement is
> the
>> most efficient way to solve our problem. We are aiming to avoid
> unnecessary
>> SELECT statements, especially with high isolation levels.
>>
>> Thank you,
>> Chris
>>
>>
>
>

> To expand on oj's response, you can use optimistic concurrency while
> avoiding the roundtrip by generating the row version value in your
> application rather than Transact-SQL For example:
>
> CREATE PROC UpdataMyTable
> @MyKey int,
> @MyValue int,
> @OldMyRowVersion uniqueidentifier,
> @NewMyRowVersion uniqueidentifier
> AS
> SET NOCOUNT ON
> UPDATE MyTable
> SET
> MyValue = @MyValue,
> MyRowVersion = @NewMyRowVersion
> WHERE
> MyKey = @MyKey AND
> MyRowVersion = @OldMyRowVersion
> IF @@ROWCOUNT = 0
> RAISERROR ('Data modified by another user', 16, 1)
> GO
>
> Rowversion/timestamp/NEWID necessitates that the SQL-generated value be
> returned back to the application via a SELECT or OUTPUT parameter. I
> would hardly say this is unusable since it services may applications quite
> well.
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Christian Hassa" <ttch@newsgroup.nospam> wrote in message
> news:%23xJjACAmEHA.2680@TK2MSFTNGP15.phx.gbl...
>> Hi!
>>
>> NEWID() is creating a Guid, so I would rather need to create a
>> Guid/uniqueidentifier column instead of a timestamp column.
>> Also, I would need to manage the Guid value by myself all the time
>> (INSERT/UPDATE) whereas rowversion would be managed by SQL server.
>>
>> Sure I can create my own infrastructure for versioning/locking using
>> Guid/uniqueidentifier, but my impression is, that rowversion is exactly
>> there for this reason, so why should I do it on my own? Isn't there any
>> meaningful way how Microsoft intended the rowversion field to be used, or
>> is it unusable by design?
>>
>> Chris
>>
>>
>>> You can create a timestamp column and use NEWID() as the value.
>>>
>
>

On Sat, 11 Sep 2004 19:09:57 +0200, Christian Hassa wrote:

>Hi Dan,
>
>thanks for following up on this thread.
>
>As I outlined in my previous response to oj, I can figure out how to use
>Guids managed by the client to do optimistic locking (thanks for the code
>sample anyway).
>
>However, fields of type "rowversion" (currently synonymous to timestamp in
>SQL server but different from Guid/uniqueidentifier) are not managed by the
>client but by SQL server. Therefore the solution oj and you are proposing
>(and which I also figured out already) is neither what I am looking for nor
>is it utilizing SQL servers rowversion feature.
>
>This is a similar question like where to manage identities - especially int
>identities: on the server side (using autoinc ints) or on the client side
>(having to synchronize distributed ID generators on the client side somehow
>in case of int identities).
>
>So my question still remains: *if* I want to use the rowversion (synonym for
>timestamp) feature of Microsoft SQL server, where I define a column of type
>"rowversion" for a table, that will be managed then by SQL server: *what* is
>the suggested way to retrieve the updated timestamp after updating/inserting
>into a table?

>In my opinion a similar function, like Scope_Identity() provided for server
>generated identity values, should be available for server generated
>rowversion/timestamp values.

> I think it is quite inefficient if I have to
>issue a second SELECT statement (while keeping the updated record locked) in
>order to retrieve the newly generated timestamp. We are working on a 2TB
>database with several db-operations per second, we are simply not allowed to
>lock certain tables for selecting back our new rowversion.