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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Igor Raytsin (n&i_at_cyberus.ca)
Date: 03/02/04 

Date: Mon, 1 Mar 2004 23:02:30 -0500

Your scripts only simulate your problem. They do not conflict with each
other, and you don't like it, right?
Can you tell us what do you want to achieve? There could be another solution
already.

There is another 'workaround'. You could do this in your first script :
SELECT *
FROM bla (xlock, rowlock)
WHERE nome = (Select nome from bla where id=1)

But yes, SQL Server Optimizer might chose different execution plan when your
database grows.

I would recommend you to read BOL - 'Locking' section.
>From 'Key-Range Locking' topic:
"Serializability requires that any query executed during a transaction must
obtain the same set of
rows if it is executed again at some later point within the same
transaction."

That is what happening with your scripts.

Igor Raytsin

"Francisco D. Kurpiel" <francisco.kurpiel@westbest.com.br> wrote in message
news:%23K5S9$5$DHA.392@TK2MSFTNGP12.phx.gbl...
> I appreciate your help.
>
> Your message helps me to understand my problem. The first query locks one
> index and the second query data using another index. I have made some
> additional tests forcing the index on the second query to use the same
index
> witch is used by the first query and the locking happened as expected.
>
> This behavior appears to be very strange to me. If some query locks the
> table using some index, another query will ignore these locks if the
> optimizer chooses another index? We know that the query plan is dependent
on
> the size of the tables evolved on the query. So, when my database grows,
the
> query analyzer may select other indexes to run the query, changing the
> concurrency behavior? This is more like a bug than a programmer
> responsibility.
>
> I have analyzed your suggestions:
>
> * Replace ROWLOCK hint with TABLOCKX
> My database will get big and will be accessed by a lot of users. I cannot
> use this solution.
>
> * Add column to the table
> My production tables have additional columns. But I have this locking
> problem because I have indexes on some tables with specific objectives. I
> have a where clause on some proc that will use one index and needs to lock
> resources, and other queries that will access this tables using other
> indexes. May I force all procs to use the same index using a
> ‘index=something’ hint. But indexes only will be useful they if they will
be
> used (sorry for the obvious statement). And if I use more than one index
SQL
> Server will not honor my locks…
>
> * Drop un_bla_nome unique constraint
> As I explained on last item, this is not applicable.
>
> * UPDATE bla SET id = id
> Hum… okay, you win. This works, it have xlocked all indexes and add an
> intent lock on the page. But this is like a… you know, a ‘workaround’.
>
> I also tried to replace my rowlocks by paglocks, by the first script locks
> one page and the second locks other page… I’m very confused.
>
> Do you have some link to documentation that explains this entire think?
>
> Thank you.
>
>
>

Relevant Pages

  • Re: Back end database lock-up
    ... Default Record Locking: No Locks ... - i checked and double checked that the query and form are set to no- ... The locking file is not created until a user updates the data via ...
    (comp.databases.ms-access)
  • Re: advanced question - xlock hint on record not locking select * from table
    ... The first query locks one ... index and the second query data using another index. ... I also tried to replace my rowlocks by paglocks, by the first script locks ... > So these two transaction do not interfere and you are getting ...
    (microsoft.public.sqlserver.server)
  • Re: DMW locks
    ... - Server activity ... In query statistics I can see CPU, wait times, .... ... Is it possible to include here also query statistics of locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: simple query locking too much data
    ... Locks will be released when the query completes and/or the transaction is ... You can check a given spid for an open transaction with DBCC OPENTRAN. ... This procedure holds locks a LOT ... >>> We have a table that consists of a user name and their password hash. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to get list of records since last time - through a script
    ... The most obvious thing to do is to define two global variables B and C - ... And to make two scripts, one which makes a query based on the A and B, ... the correct list layout. ...
    (comp.databases.filemaker)