Row level lock

Row level lock

Post by Joe » Thu, 27 Feb 2003 23:20:51



Hi

I have a query, which I want to get some clarity on

This is the scenario, which we are trying from VB

1. I have a transaction

1a. I am updating a table (abc)

1b. I have paused the execution before the code issues a commit using the VB
IDE

2. I have a sql client (query analyser)

2a. I am issuing a select * from abc

I notice that the select done from the sql client (query analyser) times out

This is my understanding

1. SQL Server by default tries a row lock, if the records to be affected by
the DML falls in the same page, it escalates the lock to a pagelock

2. Now in my case I am updating based on a value in the primary key column,
that means that this update will affect only 1 row, so this means that SQL
Server should apply only a row level lock

I have surfed the net on this problem and in
http://www.sql-server-performance.com/q&a36.asp page it also talks about
Lock Escalation Options, as well as the MSDN help

I have also tried this option as well, by applying with (rowlock) in the
update statement

I have also tried another option of setting the isolation property on the
connection object that I am using

Since this VB exe would be scheduled, the problem that we will face when
this code goes to prod is that the code will always enter this loop, and
keep the transaction open for a long time, in case we try to ingest huge
volumes of data across multiple files. So if another process tries to query
the same table (actually this is used extensively ) will time out

At the end of the day, I still find that the table is locked and that query
from another transaction like a sql client times out.

Can any one help me with this query please, if you may have any views on the
same problem

Thanks

Jeune

 
 
 

Row level lock

Post by Vadim Rap » Thu, 27 Feb 2003 23:32:02


J> 1. I have a transaction

J> 1a. I am updating a table (abc)

J> 1b. I have paused the execution before the code issues a commit using the VB
J> IDE

J> 2. I have a sql client (query analyser)

J> 2a. I am issuing a select * from abc

J> I notice that the select done from the sql client (query analyser) times out

J> This is my understanding

J> 1. SQL Server by default tries a row lock, if the records to be affected by
J> the DML falls in the same page, it escalates the lock to a pagelock

J> 2. Now in my case I am updating based on a value in the primary key column,
J> that means that this update will affect only 1 row, so this means that SQL
J> Server should apply only a row level lock

I don't see a problem... yes it locks one row; but in your select * from abc you want
the data from the whole table, hence including the locked row. Select gets all rows
but one, so it waits until that one is unlocked. What do you think is wrong?

Vadim

 
 
 

Row level lock

Post by Joe » Fri, 28 Feb 2003 13:03:51


Hi Vadim

This does not help, since the select * can be issued by anyone who may want
to access the table, like a production guy or even another application

Thanks

Jeune

Quote:> J> 1. I have a transaction

> J> 1a. I am updating a table (abc)

> J> 1b. I have paused the execution before the code issues a commit using
the VB
> J> IDE

> J> 2. I have a sql client (query analyser)

> J> 2a. I am issuing a select * from abc

> J> I notice that the select done from the sql client (query analyser)
times out

> J> This is my understanding

> J> 1. SQL Server by default tries a row lock, if the records to be
affected by
> J> the DML falls in the same page, it escalates the lock to a pagelock

> J> 2. Now in my case I am updating based on a value in the primary key
column,
> J> that means that this update will affect only 1 row, so this means that
SQL
> J> Server should apply only a row level lock

> I don't see a problem... yes it locks one row; but in your select * from
abc you want
> the data from the whole table, hence including the locked row. Select gets
all rows
> but one, so it waits until that one is unlocked. What do you think is
wrong?

> Vadim