Key Range Locking

(also called Next-Key Locking)

There is a way to disable the Key Range Locking mechanism under MS SQL
Server 2005 (or 2008) ?

Using MySQL, it can be disabled by enabling the
innodb_locks_unsafe_for_binlog system variable.
Using IBM DB2, it can be disabled by db2set DB2_RR_TO_RS=YES_OVERRIDE_RI

I believe that it can be disabled also with MS SQL Server, but how can I do
it ?

I want to prevent that during an INSERT statements some other rows will be
blocked.

Roberto,

This lock is used maily when using TRANSACTION ISOLATION LEVEL
SERIALIZABLE. Can you use a lower level like READ COMMITTED?


AMB


"Roberto" wrote:

> Key Range Locking
>
> (also called Next-Key Locking)
>
> There is a way to disable the Key Range Locking mechanism under MS SQL
> Server 2005 (or 2008) ?
>
> Using MySQL, it can be disabled by enabling the
> innodb_locks_unsafe_for_binlog system variable.
> Using IBM DB2, it can be disabled by db2set DB2_RR_TO_RS=YES_OVERRIDE_RI
>
> I believe that it can be disabled also with MS SQL Server, but how can I do
> it ?
>
> I want to prevent that during an INSERT statements some other rows will be
> blocked.

On May 16, 4:06 am, Roberto <Robe...@discussions.microsoft.com> wrote:
> Key Range Locking
>
> (also called Next-Key Locking)
>
> There is a way to disable the Key Range Locking mechanism under MS SQL
> Server 2005 (or 2008) ?
>
> Using MySQL, it can be disabled by enabling the
> innodb_locks_unsafe_for_binlog system variable.
> Using IBM DB2, it can be disabled by db2set DB2_RR_TO_RS=YES_OVERRIDE_RI
>
> I believe that it can be disabled also with MS SQL Server, but how can I do
> it ?
>
> I want to prevent that during an INSERT statements some other rows will be
> blocked.

What kind of problem are you tying to solve? Different RDBMS have
different ways of solving concurrency problems.

"Alex Kuznetsov" wrote:

> On May 16, 4:06 am, Roberto <Robe...@discussions.microsoft.com> wrote:
> > Key Range Locking
> >
> > (also called Next-Key Locking)
> >
> > There is a way to disable the Key Range Locking mechanism under MS SQL
> > Server 2005 (or 2008) ?
> >
> > Using MySQL, it can be disabled by enabling the
> > innodb_locks_unsafe_for_binlog system variable.
> > Using IBM DB2, it can be disabled by db2set DB2_RR_TO_RS=YES_OVERRIDE_RI
> >
> > I believe that it can be disabled also with MS SQL Server, but how can I do
> > it ?
> >
> > I want to prevent that during an INSERT statements some other rows will be
> > blocked.
>
> What kind of problem are you tying to solve? Different RDBMS have
> different ways of solving concurrency problems.
>

Usually I use the READ UNCOMMITTED Isolation level.
and after under DB2 I use row locking clause in the select statement.

"SELECT .... FOR READ ONLY " if I don't want any kind of lock at row level
or
"SELECT ... WITH RS USE AND KEEP EXCLUSIVE LOCKS" when I want lock a row on
the table.

under db2 this works fine.

I need to replicate this environment under SQL 2005 (but I don't have
experience about it )

My application is a 100 % J2ee Application Running under JBoss/WAS/SAP
Netweaver...

Thank a lot for the reply and sorry the delay in this reply..

On May 26, 2:34 am, Roberto <Robe...@discussions.microsoft.com> wrote:
> "Alex Kuznetsov" wrote:
> > On May 16, 4:06 am, Roberto <Robe...@discussions.microsoft.com> wrote:
> > > Key Range Locking
>
> > > (also called Next-Key Locking)
>
> > > There is a way to disable the Key Range Locking mechanism under MS SQL
> > > Server 2005 (or 2008) ?
>
> > > Using MySQL, it can be disabled by enabling the
> > > innodb_locks_unsafe_for_binlog system variable.
> > > Using IBM DB2, it can be disabled by db2set DB2_RR_TO_RS=YES_OVERRIDE_RI
>
> > > I believe that it can be disabled also with MS SQL Server, but how can I do
> > > it ?
>
> > > I want to prevent that during an INSERT statements some other rows will be
> > > blocked.
>
> > What kind of problem are you tying to solve? Different RDBMS have
> > different ways of solving concurrency problems.
>
> Usually I use the READ UNCOMMITTED Isolation level.
> and after under DB2 I use row locking clause in the select statement.
>
> "SELECT .... FOR READ ONLY " if I don't want any kind of lock at row level
> or
> "SELECT ... WITH RS USE AND KEEP EXCLUSIVE LOCKS" when I want lock a row on
> the table.
>
> under db2 this works fine.
>
> I need to replicate this environment under SQL 2005 (but I don't have
> experience about it )
>
> My application is a 100 % J2ee Application Running under JBoss/WAS/SAP
> Netweaver...
>
> Thank a lot for the reply and sorry the delay in this reply..

in 2005 you can use snapshot isolation, which in some cases gives you
better performance than READ UNCOMMITTED, and also provides consistent
data, no dirty reads. If you want to lock the rows which you select,
you can use REPEATEBLE RAD or SERIQLIZABLE isolation levels, or a
UPDLOCK hint.