advanced question - 'xlock' hint on record not locking select * from table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Francisco D. Kurpiel (francisco.kurpiel_at_westbest.com.br)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 16:50:16 -0300

I have a problem with locking on sql server 2000 sp3. I select one record
from a table using the primary key on the where clause, inside a transaction
with serializable level. In another connection, I try to do a simple select
* from sametable (also using serializable transaction level) and this
command runs without locking, when it should wait for the exclusive lock
that is held by another connection to be released.

I wrote two scripts to simulate my problem. The first creates a table,
populate it, starts a connection and selects a record using the xlock hint
leaving the transaction and the locks open. Another query analyzer must be
opened (leaving this also open) to run the second script. This script do a
simple select * from tbl. This script returns all records, including the
records that is xlocked on the another connection.

SCRIPT 1

if exists (select 1 from sysobjects where name = 'bla')

            drop table bla

go

create table bla (

            id int not null,

            nome nvarchar(256) not null,

            constraint pk_bla primary key clustered (id),

            constraint un_bla_nome unique (nome)

)

go

insert into bla values (1, 'Some thing')

insert into bla values (2, 'Some other thing')

go

set transaction isolation level serializable

begin transaction

select *

from bla (xlock, rowlock)

where id=1

execute sp_lock @@spid

--rollback transaction

SCRIPT 2 (must be run on another query analyzer window)

set transaction isolation level serializable

begin transaction

select *

from bla (holdlock, rowlock)

execute sp_lock @@spid

rollback transaction

Relevant Pages

  • advanced question - xlock hint on record not locking select * from table
    ... I have a problem with locking on sql server 2000 sp3. ... In another connection, I try to do a simple select ... opened to run the second script. ... insert into bla values ...
    (microsoft.public.sqlserver.server)
  • Re: dialup solution (as seconary connection / iptables )
    ... ip-down is launched by pppd only after the PPP link no longer available ... first with MINICOM or is PAP and script not mutualy exclusive? ... do have to "make the connection first with MINICOM." ... If you use the Linux box and route the news IP address to it on each ...
    (comp.os.linux.networking)
  • Re: mysqli connections and oop
    ... need to do all processing in a script before I display a page that will ... But it will most probably NOT terminated the execution of the script. ... this means that NO lasting connection is build ... Why does it not carry the mysqli connection? ...
    (comp.lang.php)
  • Re: mysqli connections and oop
    ... And here is the script that execute this ... lose a mysqli connection upon the reload of the page? ...
    (comp.lang.php)
  • Re: Does apache stop a script mid execution ?
    ... If the user calls a time consuming script and then stops or refreshes ... How does it relate to e.g. a script performing a large mysql query? ... 2.b) how the connection is set up. ... the server stops the script execution. ...
    (comp.lang.php)