Databases
StackOverflow2013
Postsdbo
POSELECT FOR UPDATE with SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT FOR UPDATE with SQL Server
    primarykey
    data
    text
    <p>I'm using a Microsoft SQL Server 2005 database with isolation level <code>READ_COMMITTED</code> and <code>READ_COMMITTED_SNAPSHOT=ON</code>.</p> <p>Now I want to use:</p> <pre><code>SELECT * FROM &lt;tablename&gt; FOR UPDATE </code></pre> <p>...so that other database connections block when trying to access the same row "FOR UPDATE".</p> <p>I tried: </p> <pre><code>SELECT * FROM &lt;tablename&gt; WITH (updlock) WHERE id=1 </code></pre> <p>...but this blocks all other connections even for selecting an id other than "1".</p> <p>Which is the correct hint to do a <code>SELECT FOR UPDATE</code> as known for Oracle, DB2, MySql?</p> <p><strong>EDIT 2009-10-03:</strong></p> <p>These are the statements to create the table and the index:</p> <pre><code>CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, Terminal BIGINT, Status SMALLINT ); ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id ) CREATE INDEX I108_FkTerminal ON example ( Terminal ) CREATE INDEX I108_Key ON example ( TransactionId ) </code></pre> <p>A lot of parallel processes do this <code>SELECT</code>:</p> <pre><code>SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ? </code></pre> <p><strong>EDIT 2009-10-05:</strong></p> <p>For a better overview I've written down all tried solutions in the following table:</p> <pre> mechanism | SELECT on different row blocks | SELECT on same row blocks -----------------------+--------------------------------+-------------------------- ROWLOCK | no | no updlock, rowlock | yes | yes xlock,rowlock | yes | yes repeatableread | no | no DBCC TRACEON (1211,-1) | yes | yes rowlock,xlock,holdlock | yes | yes updlock,holdlock | yes | yes UPDLOCK,READPAST | no | no I'm looking for | no | yes </pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBill Paetzke
    1. UStangens
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. USRMorrisey
      1. VTFavorite
    1. COI'm not only looking for optimizer hints. Another possible solution could be to change the isolation level, global database attributes, ... Everything (but to use a different database) is possible.
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. UStangens
    2. COWhat are you trying to do that needs such locking. It's usually better to solve with proper queries rather than server 'features'
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. USTFD
    3. COCan you please provide the query that you are using and the DDL of the table(s), including any Keys and Indexes.
      singulars
      1. POSELECT FOR UPDATE with SQL Server
      1. USRBarryYoung
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload