469,948 Members | 2,952 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,948 developers. It's quick & easy.

serializable read and lock mode

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
Jul 20 '05 #1
3 5502
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
Jul 20 '05 #2

"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
Jul 20 '05 #3
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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Magnus Byne | last post: by
2 posts views Thread by xixi | last post: by
reply views Thread by Bruce Pullen | last post: by
5 posts views Thread by harborboy76 | last post: by
3 posts views Thread by Raghavendra Tilve | last post: by
3 posts views Thread by Raj | last post: by
6 posts views Thread by michael.spoden | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.