Hi,
I am quite puzzled how SQLServer manages transactions.
Whatever the isolation level I set when performing an insertion, other
connections do not have access to the table in select mode.
Example in SQL Analyzer:
create table foo (
id numeric(10),
data varchar(100)
)
On Connection 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
insert into foo(id,data) values (1,'data');
On Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from foo
-> QUERY HANGS
On Connection 1
COMMIT
On Connection 2
Get the result
Using READ COMMITTED level, I was expecting not to lock the table when
performing the select.
Thanks in advance for your help,
Cedric 4 3612
(ex***@yahoo.fr ) writes: I am quite puzzled how SQLServer manages transactions. Whatever the isolation level I set when performing an insertion, other connections do not have access to the table in select mode.
Example in SQL Analyzer: create table foo ( id numeric(10), data varchar(100) )
On Connection 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRANSACTION insert into foo(id,data) values (1,'data');
On Connection 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED select * from foo -> QUERY HANGS
On Connection 1 COMMIT
On Connection 2 Get the result
Using READ COMMITTED level, I was expecting not to lock the table when performing the select.
Why not? READ COMMITTED means just that, read committed data, and there
is uncommitted data in the table.
You can access the uncommitted data if you change the isolation level
for connection 2 to READ UNCOMMITTED.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Ok thanks,
However the question is how can I avoid dirty reads i.e. uncommitted
data without being locked ?
This corresponds to the default behaviour in Oracle.
Thanky you
Concurrency control in SQL Server 2000 is done using locking. SQL Server
2005 introduces a new feature, snapshot isolation, that operates more like
the Oracle default you mentioned. There is a link to a whitepaper describing
the snapshot isolation feature as it is in SQL Server 2005 Beta 2 here: http://msdn.microsoft.com/SQL/2005/2...s/default.aspx
--
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
The scan from the second connection has to wait on the lock on the newly
insert row from the uncommitted transaction in connection1 because under
READ COMMITTED isolation it can't see dirty data.
The next SQL Server release will provide a new isolation level named
SNAPSHOT that will allow the second connection not to block on the
uncommited insert from the first connection, much like Oracle's scan
behavior.
--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<ex***@yahoo.fr > wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com... Hi, I am quite puzzled how SQLServer manages transactions. Whatever the isolation level I set when performing an insertion, other connections do not have access to the table in select mode.
Example in SQL Analyzer: create table foo ( id numeric(10), data varchar(100) )
On Connection 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRANSACTION insert into foo(id,data) values (1,'data');
On Connection 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED select * from foo -> QUERY HANGS
On Connection 1 COMMIT
On Connection 2 Get the result
Using READ COMMITTED level, I was expecting not to lock the table when performing the select.
Thanks in advance for your help, Cedric This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: noah |
last post by:
Does PHP have a feature to associate Cookie sessions with a persistent
database connection that will allow a single transaction across
multiple HTTP requests?
Here is how I imagine my process: I have an series of interactive HTML
forms. The user begins a Cookie session. A database connection is
opened and a transaction is begun. After the user goes through any
number of pages where they update the database they finish on a page
where...
|
by: Krzysztof Rozmus |
last post by:
Hi,
I have stored procedure (MS SQL Server 2000) which operates
on around 600 000 rows (SELECT, UPDATE, INSERT)
and executes in 5 minutes,
when I put it in SQL transaction it slows down to more than 5 hours (!!)
I have to admit that it is not problem with data locks (beside that
procedure
|
by: A. Tillman |
last post by:
We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is...
|
by: Eddie |
last post by:
I wondering which one of the following I should use to get the best
performance.
1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
OR
2. "WITH (NOLOCK)"
I notice that when I use the #1 "SET TRANSACTION..." it sets a lock Mode
type of "Sch-S" (Schema stability Lock) which described by SQL Books
Online as "Schema stability (Sch-S) locks do not block any transactional
locks, including exclusive (X) locks"
|
by: John Sidney-Woollett |
last post by:
I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.
In Oracle much of the application logic is abstracted away from the java
middleware layer using stored procedures within the Oracle database. There
are certain features in Oracle that appear to be missing from Postgres
which are causing us some concern, and we wondered how much we're going to
have to butcher the backend and db...
| |
by: Zeng |
last post by:
Hi,
The bigger my C# web-application gets, the more places I need to put in the
tedious retrying block of code to make sure operations that can run into
database deadlocks are re-run (retried) 3-4 times and give up if after that
it's still in deadlock. I'm very sure that many experienced people out
there already deal with this issue somehow. Is there an alternative to it?
Thanks for your comments and suggestions.
|
by: Nick Barr |
last post by:
Hi,
I am trying to gather stats about how many times a resource in our web
app is viewed, i.e. just a COUNT. There are potentially millions of
resources within the system.
I thought of two methods:
1. An extra column in the resource table which contains a count.
a. Each time a resource is viewed an UPDATE statement is run.
|
by: Christian Stooker |
last post by:
Part one:
======
Hi !
I want to use SQLite database like the FireBird database: with big
isolation level.
What's that meaning ?
I have an application that periodically check some input directory,
|
by: Ryan Liu |
last post by:
Hi,
I have few db write and read to execute, so I use transaction.
Is that a problem or is that a regular way that I only use transaction on
some cmds only, and other cmds I do not use trasaction, esp those read
actions, or even use another dbconnection to read?
BTW, what does "open table" menas? Is that table only be "open" when someone
read or write it. Once all user finish read or write the db table, the
|
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...
|
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,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
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...
| |