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
>