473,884 Members | 2,314 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5627
rkusenet (rk******@sympa tico.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******@sympa tico.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

29
5828
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using the transaction because I need a way to roll it back if any errors happen during the transaction. Unfortunately all tables affected in the long running transaction are completely locked and nobody else can access any of the affected tables while...
3
5778
by: Magnus Byne | last post by:
Hi, I have a problem using serializable transactions. In one transaction I select a single specific row from a table using a where clause (this causes it to acquire and hold a range lock). In another transaction I attempt to insert a new row into the table (which does not match the first transactions where clause), but it is blocked by the first transaction. The reading I have done on SQL Server suggests that I should be able to insert...
2
4476
by: xixi | last post by:
i am using type 4 driver with fp3 on windows 64bit server, auto commit is true, cursor is CS type. i have a sensitive , updatable cursor open and bring back a one row resultset, i get this locklist Lock Name = 0x020027011C0000000000000052 Lock Attributes = 0x00000000 Release Flags = 0x00000002 Lock Count = 1
0
3393
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in lock-wait, waiting to acquire row-level X locks. The lock-waits are behind applications that have row-level X locks on different rows (honestly). Both executing and lock-waiting applications have been granted IX table locks.
5
11112
by: harborboy76 | last post by:
Hi all, First of all, just want to let you know that I'm coming from Informix background, and we just recently migrated to DB2. It's been a great new learning experience. For the time being, I have some questions regarding LOCK TIMEOUT and its compatibility with LOCK MODE WAIT in INFOMIX. Last week, I just found out that the following statement was complied and built in Stored Procedure SET LOCK MODE TO WAIT 5;
3
11485
by: Raghavendra Tilve | last post by:
Unable to serialize the session state. Please note that non-serializable objects or MarshalByRef objects are not permitted when session state mode is 'StateServer' or 'SQLServer'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: Unable to...
3
6683
by: Raj | last post by:
I created a refresh deferred MQT, and during full refresh there were 4 or 5 lock waits, all waiting on a 'S' lock on Internal Catalog Cache ? Can some one explain how to prevent this from happening?
2
5876
by: shenanwei | last post by:
DB2 V8.2 on AIX, type II index is created. I see this from deadlock event monitor. 5) Deadlocked Connection ... Participant no.: 2 Lock wait start time: 09/18/2006 23:04:09.911774 ...... Deadlocked Statement: Type : Dynamic Operation: Execute
6
7729
by: michael.spoden | last post by:
Hi, how can I fix lock-waits during an online backup? Is an online backup in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux. The command to perform the backup is: db2 backup db BPEDB online to / var/tmp During the backup the application hangs on just one table
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11168
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10869
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7985
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7137
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6009
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4623
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4231
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.