Re: Row level locking

Generally speaking, when coding an application you should ALWAYS use the primary key as the identifier to update an individual row you have at your front end app. It's best practice.

Secondly, SQL Server has row level locking as part of its armoury, however it will not always choose to use it. On a small table, it may work out that a table lock is more efficient.

If you want to force this code to go ahead, try setting the TRANSACTION ISOLATION LEVEL to READ_UNCOMMITTED which neither issues nor honours locks. Beware! If it is possible that 2 connections could update the same row at the same time then you could have a consistency problem!

--

Ian Posner
MindQuest Solutions Ltd
iposner_at_dial.pipex.com
perto wrote in message <7b1dc5$caj$1_at_e3k.asi.ansaldo.it>...


>Hi,

>I'm new to MS SS7.0 and I can't make him work.


>Somebody knows how to perform two update statement on the same table?

>

>I start two different sessions:

>Session 1:

>begin tran

>update table set status = 'X' where field = 1

>

>Session 2:

>begin tran

>update table set status = 'Y' where field = 2

>

>Session 2 remains blocked by Session 1. Why? The two update statement works

>on different rows. SS7.0 has the row level locking feature, why an update

 on


>a row can block an update on another row?


>

>I found a workaround for the problem: everything works OK only when "field"

>is primary key, but this is a very bad limitation.

>

>Thanks,

>Alberto Pertotti

>

>