optimistic locking

we have an old Win NT4/SQL Server 6.5 server outhere
the size of the database has been increasing over the years(and all the
data is used daily), so we`ve been looking to upgrade the hardware or
move to a virtual mashine
but a consultant we hired on another project thinks that the main reason
for the poor performance is the pessimistic locking used by the DB, and
recomends upgrade to Oracle
as i have no Oracle expirience and SQL Server is easyer to install and
surely easyer to upgrade to in this case i`m looking for a more
optimistic version
does 7.0, 2000 and 2005 support optimistic locking ?
(we have an unused 7.0 1 cpu licence)
"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
>
Don't go to a VM, at least not for your end state. Upgrade SQL Server to
2000 or 2005 (when released), and then move the databse to a new server
running Windows Server 2003.

> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
hardware should fix your problem.

> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version
>
Migrating the application to Oracle will be a _whole_ lot harder than
upgrading SQL Server and Windows.

> does 7.0, 2000 and 2005 support optimistic locking ?
>
SQL Server 2005 supports a locking model very similar to Oracle's model. So
on SQL Server 2005 you could actually test which concurrency model works
best for your application. But you won't need to. The hardware and
software upgrade should fix you up.
David
|||SQL 7.0 and higher gives you the option to choose a locking model (see
BOL for Isolation Levels), not so sure about 6.5. But before going for
a upgrade make sure it's actually SQL Server using pessimistic locking
and not your application. If had this kind of issues and most of the
times it's things like locking hints in the application code or using
commands like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. In this
case upgrading the database won't solve anything.
M
|||I would consider this advice complete crap. Any good consultant should be
able to justify such a decision in concrete terms. Was that done? What is
the impact analysis? Where is the cost/benefit analysis? As David
suggested, an upgrade to the current version will likely do wonders for your
system (but it will likely involve re-tuning the application as well as
extensive testing).
Since you're still using 6.5, the system can't be working all that poorly
now, can it?
|||> does 7.0, 2000 and 2005 support optimistic locking ?
All versions of SQL Server support optimistic concurrency control because
this is handled via application code. With optimistic concurrency, the
application holds minimal locks based on the assumption that it is unlikely
that data are modified by other sessions. The application checks to see if
another user modified data before permitting the update. For example:
UPDATE MyTable
SET DataValue = @.NewDataValue
WHERE PK= @.PK AND
DataValue = @.OldDataValue
IF @.@.ORWCOUNT = 0
BEGIN
RAISERROR ('Data was updated or deleted by another user', 16, 1)
END
This is in contrast with pessimistic locking where the application relies on
the database to control concurrency using locking.
In SQL 6.5, a page was the most granular level of locking (ignoring insert
row-level locking for the purposes of discussion). This exacerbated
concurrency problems since unrelated data was also locked. Row-level
locking is provided in later versions so concurrency is improved, even with
pessimistic locking. Consequently, you might get some relief by upgrading
with no application changes but a lot depends on the details of your app. I
suggest you consider upgrading to SQL 7 before downgrading to another DBMS

As David mentioned, SQL Server 2005 introduces the READ_COMMITTED_SNAPSHOT
isolation level, which provides row versioning and statement-level read
consistency. This may further improve concurrency but you need to be aware
that an application that employs pessimistic concurrency might make
concurrency assumptions that are not valid under the READ_COMMITTED_SNAPSHOT
isolation level.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)
|||Fred wrote:
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all
> the data is used daily), so we`ve been looking to upgrade the
> hardware or move to a virtual mashine
> but a consultant we hired on another project thinks that the main
> reason for the poor performance is the pessimistic locking used by
> the DB, and recomends upgrade to Oracle
Does he only *think* this is the reason or did he present hard facts to
backup this statement? If not, I'd first start to investigate the
performance of your current setup. Maybe you find an easy solution to
speed up things such as an index that is missing etc.

> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more
> optimistic version
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)
Kind regards
robert
|||That is right. SQL Server supports a feature called RCSI. It is a database
option. You can use it to change your concurrency control model without
changing your application. However, as David pointed out here, you may not
even need to.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uHk41UyzFHA.3780@.TK2MSFTNGP12.phx.gbl...
> "Fred" <fred@.ilovespam.com> wrote in message
> news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Don't go to a VM, at least not for your end state. Upgrade SQL Server to
> 2000 or 2005 (when released), and then move the databse to a new server
> running Windows Server 2003.
>
> Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
> hardware should fix your problem.
>
> Migrating the application to Oracle will be a _whole_ lot harder than
> upgrading SQL Server and Windows.
>
> SQL Server 2005 supports a locking model very similar to Oracle's model.
> So on SQL Server 2005 you could actually test which concurrency model
> works best for your application. But you won't need to. The hardware and
> software upgrade should fix you up.
> David
>

optimistic locking

I am writing an Application which is connected to a database on MSSQl
Server. For performing updates on a table I chose the way of optimistic
locking using timestamps. I use a stored procedure for updating the
values which returns either 0 or 1 to indicate if there was a conflict
or not. If there was one, the current values in the database are selected.
My stored procedure for updating data looks like this:
create procedure updateSomeTable
@.key int,
@.col1 nvarchar(100),
@.col2 datetime,
@.TS timestamp
AS
update MyDB.dbo.SomeTable
set col1 = @.col1,
col2 = @.col2
where key = @.key and TS = @.TS
if(@.@.rowcount = 0)
begin
select * from SomeTable where key = @.key
return 1
end
return 0
Do I need any transactions within souch a procedure? As far as I can
think of, I don't need any... Is this the typical way to perform this
task, or are there much more common ways to do this?
Thanks
Regards
StephanNot really. The UPDATE command is in an implicit transaction, and it is the
nature of the beast of opimistic locking that a user might change it after
you retrieve it (it is optimistic after all.)
Not sure that I would just conditionally receive the row. The return count
here is not of much value, since you already have to decide whether or not
to fetch a dataset which may or may not be returned. I would just always
return the row, which takes care of any trigger modifications (if there were
any, usually this in not a big deal) but also to handle the new timestamp.
Once you do the update it will change, meaning you will have to go fetch at
least the timestamp after the modification.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
news:utkvIuQIGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I am writing an Application which is connected to a database on MSSQl
>Server. For performing updates on a table I chose the way of optimistic
>locking using timestamps. I use a stored procedure for updating the values
>which returns either 0 or 1 to indicate if there was a conflict or not. If
>there was one, the current values in the database are selected.
> My stored procedure for updating data looks like this:
> create procedure updateSomeTable
> @.key int,
> @.col1 nvarchar(100),
> @.col2 datetime,
> @.TS timestamp
> AS
> update MyDB.dbo.SomeTable
> set col1 = @.col1,
> col2 = @.col2
> where key = @.key and TS = @.TS
> if(@.@.rowcount = 0)
> begin
> select * from SomeTable where key = @.key
> return 1
> end
> return 0
> Do I need any transactions within souch a procedure? As far as I can think
> of, I don't need any... Is this the typical way to perform this task, or
> are there much more common ways to do this?
> Thanks
> Regards
> Stephan|||Ok, so my thoughts of not to need an explicit transaction was ok...
As you said it is definitely better to fetch the new dataset immediately
after inserting it. But the chances exist, that someone will update this
row between my insert and the fetch. But this probability should rather
be negligible.
Are there any other issues besides these with this procedure?
Louis Davidson schrieb:
> Not really. The UPDATE command is in an implicit transaction, and it is t
he
> nature of the beast of opimistic locking that a user might change it after
> you retrieve it (it is optimistic after all.)
> Not sure that I would just conditionally receive the row. The return coun
t
> here is not of much value, since you already have to decide whether or not
> to fetch a dataset which may or may not be returned. I would just always
> return the row, which takes care of any trigger modifications (if there we
re
> any, usually this in not a big deal) but also to handle the new timestamp.
> Once you do the update it will change, meaning you will have to go fetch a
t
> least the timestamp after the modification.
>|||> after inserting it. But the chances exist, that someone will update this
> row between my insert and the fetch. But this probability should rather be
> negligible.
This really isn't a big deal at all. The idea behind optimistic locking is
that you can get the rows, hold onto them for a long time, and still know
that they are good by the timestamp. You just accept this as the risk
(obviously the risk should be evaluated based on the data integrity
requirements).
I am going to assume that this is for 2000. If it is 2005, we could extend
using TRY...CATCH. You would possibly want transactions for 2005 with
TRY...CATCH because of how triggers behave. In 2000 an error/rollback in a
trigger just halted everything. If you are in a TRY...CATCH block, that
changes.
Anyhow, I wouldn't put the database name as a prefix, unless it is in a
different one from the tables. It makes it harder to use it with a
different named database, like for testing if you don't have the ability to
have multiple servers. I think this will work fine. I added a check to see
if the row had been deleted, though you will also know that because no rows
will be returned :)
create procedure updateSomeTable
@.key int,
@.col1 nvarchar(100),
@.col2 datetime,
@.TS timestamp
AS
declare @.rowcount int
update dbo.SomeTable
set col1 = @.col1,
col2 = @.col2
where key = @.key
and TS = @.TS
set @.rowcount = @.@.rowcount
select col1, col2
from dbo.SomeTable
where key= @.key
--this means that the row has been deleted
if @.rowcount = 1 --this means it has been updated
return 1
else
return 0
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
news:%235J5fSSIGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Ok, so my thoughts of not to need an explicit transaction was ok...
> As you said it is definitely better to fetch the new dataset immediately
> after inserting it. But the chances exist, that someone will update this
> row between my insert and the fetch. But this probability should rather be
> negligible.
> Are there any other issues besides these with this procedure?
> Louis Davidson schrieb:

optimistic locking

we have an old Win NT4/SQL Server 6.5 server outhere
the size of the database has been increasing over the years(and all the
data is used daily), so we`ve been looking to upgrade the hardware or
move to a virtual mashine
but a consultant we hired on another project thinks that the main reason
for the poor performance is the pessimistic locking used by the DB, and
recomends upgrade to Oracle
as i have no Oracle expirience and SQL Server is easyer to install and
surely easyer to upgrade to in this case i`m looking for a more
optimistic version
does 7.0, 2000 and 2005 support optimistic locking ?
(we have an unused 7.0 1 cpu licence)"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
>
Don't go to a VM, at least not for your end state. Upgrade SQL Server to
2000 or 2005 (when released), and then move the databse to a new server
running Windows Server 2003.

> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
hardware should fix your problem.

> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version
>
Migrating the application to Oracle will be a _whole_ lot harder than
upgrading SQL Server and Windows.

> does 7.0, 2000 and 2005 support optimistic locking ?
>
SQL Server 2005 supports a locking model very similar to Oracle's model. So
on SQL Server 2005 you could actually test which concurrency model works
best for your application. But you won't need to. The hardware and
software upgrade should fix you up.
David|||SQL 7.0 and higher gives you the option to choose a locking model (see
BOL for Isolation Levels), not so sure about 6.5. But before going for
a upgrade make sure it's actually SQL Server using pessimistic locking
and not your application. If had this kind of issues and most of the
times it's things like locking hints in the application code or using
commands like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. In this
case upgrading the database won't solve anything.
M|||I would consider this advice complete crap. Any good consultant should be
able to justify such a decision in concrete terms. Was that done? What is
the impact analysis? Where is the cost/benefit analysis? As David
suggested, an upgrade to the current version will likely do wonders for your
system (but it will likely involve re-tuning the application as well as
extensive testing).
Since you're still using 6.5, the system can't be working all that poorly
now, can it?|||> does 7.0, 2000 and 2005 support optimistic locking ?
All versions of SQL Server support optimistic concurrency control because
this is handled via application code. With optimistic concurrency, the
application holds minimal locks based on the assumption that it is unlikely
that data are modified by other sessions. The application checks to see if
another user modified data before permitting the update. For example:
UPDATE MyTable
SET DataValue = @.NewDataValue
WHERE PK= @.PK AND
DataValue = @.OldDataValue
IF @.@.ORWCOUNT = 0
BEGIN
RAISERROR ('Data was updated or deleted by another user', 16, 1)
END
This is in contrast with pessimistic locking where the application relies on
the database to control concurrency using locking.
In SQL 6.5, a page was the most granular level of locking (ignoring insert
row-level locking for the purposes of discussion). This exacerbated
concurrency problems since unrelated data was also locked. Row-level
locking is provided in later versions so concurrency is improved, even with
pessimistic locking. Consequently, you might get some relief by upgrading
with no application changes but a lot depends on the details of your app. I
suggest you consider upgrading to SQL 7 before downgrading to another DBMS

As David mentioned, SQL Server 2005 introduces the READ_COMMITTED_SNAPSHOT
isolation level, which provides row versioning and statement-level read
consistency. This may further improve concurrency but you need to be aware
that an application that employs pessimistic concurrency might make
concurrency assumptions that are not valid under the READ_COMMITTED_SNAPSHOT
isolation level.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)|||Fred wrote:
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all
> the data is used daily), so we`ve been looking to upgrade the
> hardware or move to a virtual mashine
> but a consultant we hired on another project thinks that the main
> reason for the poor performance is the pessimistic locking used by
> the DB, and recomends upgrade to Oracle
Does he only *think* this is the reason or did he present hard facts to
backup this statement? If not, I'd first start to investigate the
performance of your current setup. Maybe you find an easy solution to
speed up things such as an index that is missing etc.

> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more
> optimistic version
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)
Kind regards
robert|||That is right. SQL Server supports a feature called RCSI. It is a database
option. You can use it to change your concurrency control model without
changing your application. However, as David pointed out here, you may not
even need to.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uHk41UyzFHA.3780@.TK2MSFTNGP12.phx.gbl...
> "Fred" <fred@.ilovespam.com> wrote in message
> news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Don't go to a VM, at least not for your end state. Upgrade SQL Server to
> 2000 or 2005 (when released), and then move the databse to a new server
> running Windows Server 2003.
>
> Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
> hardware should fix your problem.
>
> Migrating the application to Oracle will be a _whole_ lot harder than
> upgrading SQL Server and Windows.
>
> SQL Server 2005 supports a locking model very similar to Oracle's model.
> So on SQL Server 2005 you could actually test which concurrency model
> works best for your application. But you won't need to. The hardware and
> software upgrade should fix you up.
> David
>

optimistic locking

we have an old Win NT4/SQL Server 6.5 server outhere
the size of the database has been increasing over the years(and all the
data is used daily), so we`ve been looking to upgrade the hardware or
move to a virtual mashine
but a consultant we hired on another project thinks that the main reason
for the poor performance is the pessimistic locking used by the DB, and
recomends upgrade to Oracle
as i have no Oracle expirience and SQL Server is easyer to install and
surely easyer to upgrade to in this case i`m looking for a more
optimistic version :)
does 7.0, 2000 and 2005 support optimistic locking ?
(we have an unused 7.0 1 cpu licence)"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
>
Don't go to a VM, at least not for your end state. Upgrade SQL Server to
2000 or 2005 (when released), and then move the databse to a new server
running Windows Server 2003.
> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
hardware should fix your problem.
> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version :)
>
Migrating the application to Oracle will be a _whole_ lot harder than
upgrading SQL Server and Windows.
> does 7.0, 2000 and 2005 support optimistic locking ?
>
SQL Server 2005 supports a locking model very similar to Oracle's model. So
on SQL Server 2005 you could actually test which concurrency model works
best for your application. But you won't need to. The hardware and
software upgrade should fix you up.
David|||SQL 7.0 and higher gives you the option to choose a locking model (see
BOL for Isolation Levels), not so sure about 6.5. But before going for
a upgrade make sure it's actually SQL Server using pessimistic locking
and not your application. If had this kind of issues and most of the
times it's things like locking hints in the application code or using
commands like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. In this
case upgrading the database won't solve anything.
M|||I would consider this advice complete crap. Any good consultant should be
able to justify such a decision in concrete terms. Was that done? What is
the impact analysis? Where is the cost/benefit analysis? As David
suggested, an upgrade to the current version will likely do wonders for your
system (but it will likely involve re-tuning the application as well as
extensive testing).
Since you're still using 6.5, the system can't be working all that poorly
now, can it?|||> does 7.0, 2000 and 2005 support optimistic locking ?
All versions of SQL Server support optimistic concurrency control because
this is handled via application code. With optimistic concurrency, the
application holds minimal locks based on the assumption that it is unlikely
that data are modified by other sessions. The application checks to see if
another user modified data before permitting the update. For example:
UPDATE MyTable
SET DataValue = @.NewDataValue
WHERE PK= @.PK AND
DataValue = @.OldDataValue
IF @.@.ORWCOUNT = 0
BEGIN
RAISERROR ('Data was updated or deleted by another user', 16, 1)
END
This is in contrast with pessimistic locking where the application relies on
the database to control concurrency using locking.
In SQL 6.5, a page was the most granular level of locking (ignoring insert
row-level locking for the purposes of discussion). This exacerbated
concurrency problems since unrelated data was also locked. Row-level
locking is provided in later versions so concurrency is improved, even with
pessimistic locking. Consequently, you might get some relief by upgrading
with no application changes but a lot depends on the details of your app. I
suggest you consider upgrading to SQL 7 before downgrading to another DBMS
:)
As David mentioned, SQL Server 2005 introduces the READ_COMMITTED_SNAPSHOT
isolation level, which provides row versioning and statement-level read
consistency. This may further improve concurrency but you need to be aware
that an application that employs pessimistic concurrency might make
concurrency assumptions that are not valid under the READ_COMMITTED_SNAPSHOT
isolation level.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred" <fred@.ilovespam.com> wrote in message
news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all the
> data is used daily), so we`ve been looking to upgrade the hardware or move
> to a virtual mashine
> but a consultant we hired on another project thinks that the main reason
> for the poor performance is the pessimistic locking used by the DB, and
> recomends upgrade to Oracle
> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more optimistic
> version :)
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)|||Fred wrote:
> we have an old Win NT4/SQL Server 6.5 server outhere
> the size of the database has been increasing over the years(and all
> the data is used daily), so we`ve been looking to upgrade the
> hardware or move to a virtual mashine
> but a consultant we hired on another project thinks that the main
> reason for the poor performance is the pessimistic locking used by
> the DB, and recomends upgrade to Oracle
Does he only *think* this is the reason or did he present hard facts to
backup this statement? If not, I'd first start to investigate the
performance of your current setup. Maybe you find an easy solution to
speed up things such as an index that is missing etc.
> as i have no Oracle expirience and SQL Server is easyer to install and
> surely easyer to upgrade to in this case i`m looking for a more
> optimistic version :)
> does 7.0, 2000 and 2005 support optimistic locking ?
> (we have an unused 7.0 1 cpu licence)
Kind regards
robert|||That is right. SQL Server supports a feature called RCSI. It is a database
option. You can use it to change your concurrency control model without
changing your application. However, as David pointed out here, you may not
even need to.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uHk41UyzFHA.3780@.TK2MSFTNGP12.phx.gbl...
> "Fred" <fred@.ilovespam.com> wrote in message
> news:OWAhM$xzFHA.1924@.TK2MSFTNGP14.phx.gbl...
>> we have an old Win NT4/SQL Server 6.5 server outhere
>> the size of the database has been increasing over the years(and all the
>> data is used daily), so we`ve been looking to upgrade the hardware or
>> move to a virtual mashine
> Don't go to a VM, at least not for your end state. Upgrade SQL Server to
> 2000 or 2005 (when released), and then move the databse to a new server
> running Windows Server 2003.
>> but a consultant we hired on another project thinks that the main reason
>> for the poor performance is the pessimistic locking used by the DB, and
>> recomends upgrade to Oracle
> Hmm. Very very unlikely. SQL 2000 or 2005 on Windows 2003 and modern
> hardware should fix your problem.
>> as i have no Oracle expirience and SQL Server is easyer to install and
>> surely easyer to upgrade to in this case i`m looking for a more
>> optimistic version :)
> Migrating the application to Oracle will be a _whole_ lot harder than
> upgrading SQL Server and Windows.
>> does 7.0, 2000 and 2005 support optimistic locking ?
> SQL Server 2005 supports a locking model very similar to Oracle's model.
> So on SQL Server 2005 you could actually test which concurrency model
> works best for your application. But you won't need to. The hardware and
> software upgrade should fix you up.
> David
>