serializable read and lock mode

[rkusenet]

Hi,

Version Info: SQLSERVER 2000 SP3

I am trying to understand how SQL Server works with SERIALIZABLE read.
I am fairly new to SQL Server, having mainly worked with Informix.
We are getting lock timeout error in our application. Our lock timeout
is set to 20 seconds. I am trying to investigate why locks are held for
such a long time in the first place.

I believe SQLServer tries to put keylocks as much as possible for
ensuring serializibility. For e.g.
SELECT column_b
from table_a
where column_c between 1000 and 1100

what if column_c has no index. Does it switch over the data row.
And what if a table has no index. Will it go for table lock, as it
use to do in earlier versions.

TIA.

--

email id is bogus

[Erland Sommarskog]

rkusenet (rk******@sympatico.ca) writes:

I am trying to understand how SQL Server works with SERIALIZABLE read.
I am fairly new to SQL Server, having mainly worked with Informix.
We are getting lock timeout error in our application. Our lock timeout
is set to 20 seconds. I am trying to investigate why locks are held for
such a long time in the first place.

I believe SQLServer tries to put keylocks as much as possible for
ensuring serializibility. For e.g.
SELECT column_b
from table_a
where column_c between 1000 and 1100

For this query, SQL Server will take a range lock if column_c is
indexed.

If there is no index, SQL Server will have to lock the table as far as
I can understand, as serializable means that you to do permit anyone
to insert a row with a value in this range until the transaction is
committed.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rkusenet]

"Erland Sommarskog" <so****@algonet.se> wrote

I am trying to understand how SQL Server works with SERIALIZABLE read.
I am fairly new to SQL Server, having mainly worked with Informix.
We are getting lock timeout error in our application. Our lock timeout
is set to 20 seconds. I am trying to investigate why locks are held for
such a long time in the first place.

I believe SQLServer tries to put keylocks as much as possible for
ensuring serializibility. For e.g.
SELECT column_b
from table_a
where column_c between 1000 and 1100

For this query, SQL Server will take a range lock if column_c is
indexed.

If there is no index, SQL Server will have to lock the table as far as
I can understand, as serializable means that you to do permit anyone
to insert a row with a value in this range until the transaction is
committed.

I checked it and it is indeed correct. In the above example, if there
is no index on column_c (even if there are indexes on other fields),
a table lock will be used to achieve serializability.

Are there any other conditions when a table lock will be used.

Thanks for your help.

--

email id is bogus

[Erland Sommarskog]

rkusenet (rk******@sympatico.ca) writes:

I checked it and it is indeed correct. In the above example, if there
is no index on column_c (even if there are indexes on other fields),
a table lock will be used to achieve serializability.

Are there any other conditions when a table lock will be used.

Well, an obvious case is

SELCET * FROM tbl

For the case you an bounded interval, I don't really know. I would expect
that a range lock will do, but if the query is complex there may be good
reasons to use a table lock instead. But that may be the logic of the query
is such that it actually covers the entire table.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp