Is a select statement a transaction?
a
is this a transaction even though there is not a begin transaction
statement?
If it is not a transaction, will the statement place any locks on the table?
Thanks.
Mike C#
SELECT queries do take advantage of SQL Server's locking mechanisms.
Consider if I am executing an UPDATE to the same row you are retrieving in
your query at the same time. If SELECT didn't lock the row you could
retrieve inaccurate data. You can override SQL's locking mechanisms with
query hints, such as NOLOCK, if you need to.
a
I understand the default isolation level is Read Committed for select
statements. What exactly does that mean? During an update, The record is
locked, it is updated, the transaction log is updated, the record is
unlocked? With read committed it will wait until the transaction is
committed and the x lock released to read the record. If read uncommitted
it will read the record even though the record is x locked and the
transaction is not committed and may be rolled back. If the row is being
updated when the query wants it's data it may return inaccurate results
because it may be in between the delete and insert actions that occur on an
update. The read statement can issue a shared lock, but from BOL:
Alternatively, if a shared (S) lock has been applied to a resource, other
transactions can also acquire a shared lock or an update (U) lock on that
item, even if the first transaction has not completed. However, other
transactions cannot acquire an exclusive lock until the shared lock has been
released.
So the update can proceed even if a shared lock is placed on the record?
And even if the select statement is placing locks would these be significant
enough to cause update statements to timeout? Would placing nolocks in
every select statement on SQL 2005 really prevent update statements from
timing out? I really think that is not the solution. And since the fix is
planned for next weeks release and the next release is not for 3 months, (I
just became aware of this on an auxillary application that I have recently
started supporting) I need to be fully versed to either support implementing
further resolutions or become in agreeement with the solution.
"Mike C#" <x...@xyz.com> wrote in message
news:OiDCpEMg...@TK2MSFTNGP02.phx.gbl...
Tony Rogerson
Take a look at
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx for
some background reading.
select * from table
The above will put a share lock as it goes to make sure it's not being
updated by anybody else and to prevent somebody from updating it as you are
reading it; it does an aquire - release as it reads through rather than an
acquire acquire acquire and releases at the end.
Look at READ_COMMITTED_SNAPSHOT; this stops the readers from blocking - they
just read the last committed value instead.
There are considerations around using this but if you are using NOLOCK then
you shouldn't have a problem
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
<a> wrote in message news:eXIxHtMg...@TK2MSFTNGP04.phx.gbl...
Niels Berglund
using locking mechanisms. In SQL you have two major lock types: Read
Lock, and Write Locks. A read lock is a shared lock, whereas a write
lock is exclusive. I.e you an have several users reading the same data,
but only one person can write to the data.
Now, you mentioned above about sharing locks and writing to the data. If
there is a read lock on a record, if someone wants to write to that
record SQL tries to take a write lock, but will fail as there is a read
lock. Going the other way, if someone is writing to a record and someone
wants to read that record he will be denied as there is an exclusive
write lock on the record. If someone want to read the record, well there
is an exclusive write lock so a read lock will not be granted (unless he
- the reader - runs under a less restrictive isolation level).
So how long are locks being held hen; well it depends :-) A write lock
is always held until the end of the transaction (tx):
begin tran
update some_table ... -- lock is taken here
--do some other stuff
--do some more stuff
commit tran --lock is released here
for a read lock however it depends on isolation level. When you run
under default (READ COMMITTED), the lock is held for the duration of the
statement, and is not related to an explicit transaction.
OK, I hope this helps a bit. The easiest way to come to grips with locks
etc is to code up some examples.
Niels
TheSQLGuru
statement (batch) you send in gets it's own implicit transaction done
automatically.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<a> wrote in message news:eoxmfhLg...@TK2MSFTNGP06.phx.gbl...
Mike C#
<a> wrote in message news:eXIxHtMg...@TK2MSFTNGP04.phx.gbl...
> So the update can proceed even if a shared lock is placed on the record?
> And even if the select statement is placing locks would these be
> significant enough to cause update statements to timeout? Would placing
> nolocks in every select statement on SQL 2005 really prevent update
> statements from timing out? I really think that is not the solution. And
> since the fix is planned for next weeks release and the next release is
> not for 3 months, (I just became aware of this on an auxillary application
> that I have recently started supporting) I need to be fully versed to
> either support implementing further resolutions or become in agreeement
> with the solution.
NOLOCK is not the solution in all cases. I think Tony R. posted a blog
entry about some of the problems that can occur if you aren't careful with
NOLOCK, but I don't have a link handy. It sounds as if you have a very
specific problem you're trying to address, but you haven't given a lot of
detail. Can you post more details about the problem you're encountering?
Alex Kuznetsov
it is a transaction. You can inspect the locks acquired under
different isolation levels as follows:
begin tran
select top 1 * from table
exec sp_lock
commit
a
and do not like the way these links get posted on the internet and thus the
sparse personal information, but I do love this group!
If a page is being read when an update is trying to obtain an x lock on that
page does the update instantly deadlock or does it wait? I think it would
wait, and that changing all the read queries to nolock will not resolve
update timeout issues. I do not have any details on the issue I am working
on at this time, I will be working with operations to get them Monday and
will update the group as it becomes available and if it contributes to the
discussion. I was very glad to see an autoritative and conclusive statement
in your link:
COUNT(*) query starts reading from page 1, then incrementally page by page
through to 66,846; as it traverses through the table it acquires a Shared
Intent lock on the table itself but for each page it acquires a shared lock
but the locks are acquired and released as we traverse through the table -
they are not held for the entire duration of the statement.
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:9F755A72-0D24-4E67...@microsoft.com...
Alex Kuznetsov
> If a page is being read when an update is trying to obtain an x lock on that
> page does the update instantly deadlock or does it wait?
definitely not right now. You can open up two tabs in ssms, and use
sp_lock to see for yourself.
Niels Berglund
Well, in that particular statement you won't see a lock under default
isolation level, as the lock is released after the statement have completed.
Niels
Alex Kuznetsov
Good point. You can try to wrap a WAITFOR in a UDF, and use that UDF
in a WHERE clause. This will make your SELECT execute for at least the
time specified in your WAITFOR. This might help you.
Erland Sommarskog
> If a page is being read when an update is trying to obtain an x lock on
> that page does the update instantly deadlock or does it wait?
First of all, since SQL 7 the lock granularity is rows not on pages.
The default for the UPDATE is to wait. This can be controlled by SET
LOCK_TIMEOUT which permits you to specify in milliseconds how long
the process should wait for a lock on a resource.
Note that in this case the UPDATE would get a lock timeout, not a
deadlock. A deadlock is when two processes block each other, both
waiting for locks resources held by the other process. It is possible
for an UPDATE and SELECT deadlock with other under some circumstances.
> I think it would wait, and that changing all the read queries to nolock
> will not resolve update timeout issues.
Depends on what "update timeout" issues you are seeing. The most common
timeouts you see are the defaul timeout for 30 seconds set up by
many client APIs. Note that these timeouts are not related to UPDATES.
They are just the time the clients waits for output from the server.
If you UPDATE queries times out, this can be due do blocking. But it
can also be due to poorly specicied search conditions, so it takes a
long time for SQL Server to locate the rows to update. Or it can be
that there are very many rows to update, so the update takes a very
long time.
In any case, I recommend changing to NOLOCK as a matter of routine. There
are a few surprising things that can happen. You really need to understand
the consequences with NOLOCK before you go for it.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Mike C#
news:6319bce0-80df-4bc6...@n36g2000hse.googlegroups.com...
> it is a transaction. You can inspect the locks acquired under
> different isolation levels as follows:
>
> begin tran
> select top 1 * from table
> exec sp_lock
> commit
Let's not confuse the issue here. Select queries lock rows, pages, extents,
and even tables when necessary, but a select query is not a transaction. By
definition a transaction writes to the transaction log and can be rolled
back. You can't roll back a select query:
begin tran
select top (1) * from humanresources.department
exec sp_lock
rollback
Alex Kuznetsov
Where does your definition comes from? Mine is different: "A database
transaction is a unit of interaction with an RDBMS that is treated in
a coherent and reliable way independent of other transactions." It has
nothing to do with implementation details such as transaction log. It
is true on systems that do not have a transaction log, such as Oracle,
which has undo and redo logs instead.
Clearly selects can be reliably executed independent of other
transactions. As such, they meet the definition. You can play with
isolation levels and see for yourself.
Tom Cooper
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9A5C7506...@127.0.0.1...
> (a) writes:
>
> In any case, I recommend changing to NOLOCK as a matter of routine. There
> are a few surprising things that can happen. You really need to understand
> the consequences with NOLOCK before you go for it.
>
I assume you meant "I DON'T recommend change to NOLOCK as a matter of
routine..."
Tom
Alex Kuznetsov
wrote:
I assume it too. "Could not continue scan with NOLOCK due to data
movement" is a strong reason IMO.
Mike C#
>> Let's not confuse the issue here. Select queries lock rows, pages,
>> extents,
>> and even tables when necessary, but a select query is not a transaction.
>> By
>> definition a transaction writes to the transaction log and can be rolled
>> back. You can't roll back a select query:
>>
>> begin tran
>> select top (1) * from humanresources.department
>> exec sp_lock
>> rollback
>
> Where does your definition comes from? Mine is different: "A database
> transaction is a unit of interaction with an RDBMS that is treated in
> a coherent and reliable way independent of other transactions." It has
> nothing to do with implementation details such as transaction log. It
> is true on systems that do not have a transaction log, such as Oracle,
> which has undo and redo logs instead.
>
> Clearly selects can be reliably executed independent of other
> transactions. As such, they meet the definition. You can play with
> isolation levels and see for yourself.
Unfortunately you left the second sentence out from the definition you got
from Wikipedia: "In general, a database transaction must be atomic, meaning
that it must be either entirely completed or aborted." In practice I can
start a select query in SSMS, for instance, retrieve 1/2 of the results and
cancel out of it. This does not fit the "all-or-nothing" proposition
inherent in transactions, does it? If the select query were a
"transaction", it should either all complete, or none of it complete, as it
does for every other transaction, does it not?
Or do you have another definition for transaction that states they do not
have to be atomic?
Alex Kuznetsov
You have chosen not to answer where your definition of transaction
comes from. This one: "By
definition a transaction writes to the transaction log". Is it your
own definition?
Let me repeat one more question: how would you define transactions in
Oracle, which does not have transaction logs?
> In practice I can
> start a select query in SSMS, for instance, retrieve 1/2 of the results and
> cancel out of it. This does not fit the "all-or-nothing" proposition
> inherent in transactions, does it? If the select query were a
> "transaction", it should either all complete, or none of it complete, as it
> does for every other transaction, does it not?
>
> Or do you have another definition for transaction that states they do not
> have to be atomic?
In this case your operation has failed, and yes, SQL Server did pull
the result set back, it did not un-retrieve it, nor it is required.
Nor does a transaction which modifies data provide this "either all
complete, or none of it complete" functionality in the sense you
expect. You can see for yourself:
BEGIN TRAN
UPDATE YourTable SET SomeColumn = SomeValue
SELECT SomeColumn FROM SomeTable
ROLLBACK
The changes, if any, are rolled back, yet your result set still
exists, and the reason is trivial: result sets exist outside of RDBMS
and as such atomicity does not apply to them.
Mike C#
>> Unfortunately you left the second sentence out from the definition you
>> got
>> from Wikipedia: "In general, a database transaction must be atomic,
>> meaning
>> that it must be either entirely completed or aborted."
>
> You have chosen not to answer where your definition of transaction
> comes from. This one: "By
> definition a transaction writes to the transaction log". Is it your
> own definition?
> Let me repeat one more question: how would you define transactions in
> Oracle, which does not have transaction logs?
Well, the second sentence I gave you came from your Wikipedia article. My
original definition comes from the SQL Server-specific implementation of
ACID property enforcement. "Transactions" enforce the ACID properties. All
of them. That is their one and only purpose in SQL Server, Oracle, DB2, or
anywhere else. BTW, Oracle enforces the ACID properties in transactions as
well, just FYI.
I hope I didn't bog you down with too many SQL Server
implementation-specific details, especially in a newsgroup named
"Microsoft.Public.Sqlserver.Programming"...
>> In practice I can
>> start a select query in SSMS, for instance, retrieve 1/2 of the results
>> and
>> cancel out of it. This does not fit the "all-or-nothing" proposition
>> inherent in transactions, does it? If the select query were a
>> "transaction", it should either all complete, or none of it complete, as
>> it
>> does for every other transaction, does it not?
>>
>> Or do you have another definition for transaction that states they do not
>> have to be atomic?
>
> In this case your operation has failed, and yes, SQL Server did pull
> the result set back, it did not un-retrieve it, nor it is required.
>
> Nor does a transaction which modifies data provide this "either all
> complete, or none of it complete" functionality in the sense you
> expect. You can see for yourself:
>
> BEGIN TRAN
> UPDATE YourTable SET SomeColumn = SomeValue
> SELECT SomeColumn FROM SomeTable
> ROLLBACK
>
> The changes, if any, are rolled back, yet your result set still
> exists, and the reason is trivial: result sets exist outside of RDBMS
> and as such atomicity does not apply to them.
The changes are rolled back, as you say. This is the *very* definition of
Atomic. This is exactly what transactions assure: Atomicity, Consistency,
Isolation, Durability. Transactions don't assure "Atomicity and Durability,
but not Consistency and Isolation", nor do they assure "Consistency and
Isolation, but not Atomicity and Durability". They assure all four aspects.
As you pointed out isolation levels and locking mechanisms ensure Isolation.
They do not ensure atomicity and durability. You can prove it to yourself a
number of ways:
Run a long-running SELECT query in SSMS and cancel the query halfway
through. The SELECT query will return partial results. Now try running a
long-running UPDATE statement and cancel it halfway through. Are some of
the rows updated while others are not? I.e., did you get partial results
from the UPDATE statement like you did with SELECT? Not on SQL Server you
didn't.
As for durability, your SELECT query is decidedly non-durable. Again you
can prove it to yourself by cutting power after a successful UPDATE
statement. The results are persisted and available once you turn the server
back on. Now how durable are the results of that SELECT query? Zero
durability is a good guess.
You claim that "result sets exist outside of RDBMS and as such atomicity
does not apply to them". Then by that same logic, please explain why
"isolation" applies to them? Or is it part of your definition that you are
the arbiter who hand-picks random ACID properties to make SQL statements fit
your definition of "transaction"? If result sets exist "outside the RDBMS",
then how does SQL "transaction" apply at all?
You seem to be proposing that SELECT queries, which enforce around 50% of
the ACID properties, are therefore wrapped in implicit SQL transactions.
They also cannot be committed or rolled back like real transactions. I
think maybe you should come up with a more appropriate and descriptive
terminology, maybe instead of a real "transaction", you could refer to this
behavior as a "halfaction".
Tony Rogerson
in it and you do a COUNT(*) you can still get 1 million + a few more rows
counted if the data underneath moved because of movement within the ordered
index.
If accurate results ie. at the time you read the data then you need
SERIALISABLE.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:dec4ad2e-af63-40ee...@c33g2000hsd.googlegroups.com...
Erland Sommarskog
Erland Sommarskog
> Good point. You can try to wrap a WAITFOR in a UDF, and use that UDF
> in a WHERE clause. This will make your SELECT execute for at least the
> time specified in your WAITFOR. This might help you.
CREATE FUNCTION waitie () RETURNS int AS
BEGIN
WAITFOR DELAY '00:00:01'
RETURN 1
END
=>
Msg 443, Level 16, State 14, Procedure waitie, Line 3
Invalid use of side-effecting or time-dependent operator in 'WAITFOR' within
a function.
You can still do it though, by forking out with xp_cmdshell.
Alex Kuznetsov
> "Alex Kuznetsov" <alk...@gmail.com> wrote in message
>
> news:b18cb70b-787f-49f4...@p25g2000hsf.googlegroups.com...
>
> >> Unfortunately you left the second sentence out from the definition you
> >> got
> >> from Wikipedia: "In general, a database transaction must be atomic,
> >> meaning
> >> that it must be either entirely completed or aborted."
>
> > You have chosen not to answer where your definition of transaction
> > comes from. This one: "By
> > definition a transaction writes to the transaction log". Is it your
> > own definition?
> > Let me repeat one more question: how would you define transactions in
> > Oracle, which does not have transaction logs?
>
> Well, the second sentence I gave you came from your Wikipedia article. My
> original definition comes from the SQL Server-specific implementation of
> ACID property enforcement.
Where exactly from? Can you provide a url?
>
> > The changes, if any, are rolled back, yet your result set still
> > exists, and the reason is trivial: result sets exist outside of RDBMS
> > and as such atomicity does not apply to them.
>
> The changes are rolled back, as you say. This is the *very* definition of
> Atomic. This is exactly what transactions assure: Atomicity, Consistency,
> Isolation, Durability. Transactions don't assure "Atomicity and Durability,
> but not Consistency and Isolation", nor do they assure "Consistency and
> Isolation, but not Atomicity and Durability". They assure all four aspects.
>
> As you pointed out isolation levels and locking mechanisms ensure Isolation.
> They do not ensure atomicity and durability. You can prove it to yourself a
> number of ways:
>
> Run a long-running SELECT query in SSMS and cancel the query halfway
> through. The SELECT query will return partial results.
besides that, the query will fail. it is the responsibility of the
client to discard those partial results.
> Now try running a
> long-running UPDATE statement and cancel it halfway through. Are some of
> the rows updated while others are not? I.e., did you get partial results
> from the UPDATE statement like you did with SELECT? Not on SQL Server you
> didn't.
>
> As for durability, your SELECT query is decidedly non-durable. Again you
> can prove it to yourself by cutting power after a successful UPDATE
> statement. The results are persisted and available once you turn the server
> back on. Now how durable are the results of that SELECT query? Zero
> durability is a good guess.
>
Let's apply apply your own logic to the following query, which both
modifies and selects:
BEGIN TRAN
UPDATE YourTable SET SomeColumn = SomeValue
SELECT SomeColumn FROM SomeTable
This is legal SQL, and quite a common situation. Clearly this is a
transaction by any definition, including yours. Suppose the select was
chosen as a deadlock victim and failed half way through - this is a
very common scenario. Suppose you have rolled back your transaction.
Of course your partially retrieved result set is not durable, so what?
If you really believe that SQL Server failed to ensure durability (or
any other property from ACID) for your transaction, why don't you
submit the case to Connect and see Microsoft's reaction.
> You seem to be proposing that SELECT queries, which enforce around 50% of
> the ACID properties, are therefore wrapped in implicit SQL transactions.
> They also cannot be committed or rolled back like real transactions. I
> think maybe you should come up with a more appropriate and descriptive
> terminology, maybe instead of a real "transaction", you could refer to this
> behavior as a "halfaction".
Your requirements are too stringent - I don't know any RDBMS which
implements transactions that meet your requirements.
Because your arguments are not too convincing, let me give you a
better argument supporting your point of view. If your run the
following:
SELECT c.SomeColumn, @@TRANCOUNT AS Trancount FROM data.Customers c
you will get 0 in Trancount column...
Alex Kuznetsov
> But you get that with READ COMMITTED as well, if a table has 1 million rows
> in it and you do a COUNT(*) you can still get 1 million + a few more rows
> counted if the data underneath moved because of movement within the ordered
> index.
>
> If accurate results ie. at the time you read the data then you need
> SERIALISABLE.
Or snapshot isolation, because serializable frequently causes
deadlocks.
Tony Rogerson
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:b0aea920-da83-4b50...@m36g2000hse.googlegroups.com...
Niels Berglund
> On Mar 8, 11:59 pm, "Mike C#" <x...@xyz.com> wrote:
>> "Alex Kuznetsov" <alk...@gmail.com> wrote in message
>>
>
> Your requirements are too stringent - I don't know any RDBMS which
> implements transactions that meet your requirements.
> Because your arguments are not too convincing, let me give you a
> better argument supporting your point of view. If your run the
> following:
>
> SELECT c.SomeColumn, @@TRANCOUNT AS Trancount FROM data.Customers c
>
> you will get 0 in Trancount column...
>
>
@@TRANCOUNT to prove if there is a transaction or not around won't give
you anything useful. AFAIK, @@TRANCOUNT is only incremented after an
explicit BEGIN TRAN statement. If you want to see if you have a
transaction or not, you can (in SQL 2005) use
sys.dm_tran_current_transaction:
select t,some_col, transaction_id
from sys.dm_current_transaction, some_table t
and you will see a transaction id.
Niels
Alex Kuznetsov
But that is exactly my point: to my best knowledge the only difference
between a standalone select statement, which is implicitly a
transaction in itself, and the same select explicitly preceded by
BEGIN TRAN is the behavior of @@TRANCOUNT. Everything else to my best
knowledge behaves identically.
Erland Sommarskog
> I don't know what you really are arguing about here, however bringing in
> @@TRANCOUNT to prove if there is a transaction or not around won't give
> you anything useful. AFAIK, @@TRANCOUNT is only incremented after an
> explicit BEGIN TRAN statement.
Try this:
CREATE TABLE tramscount(a int NOT NULL)
INSERT tramscount(a) VALUES(@@trancount)
SELECT a FROM tramscount
DROP TABLE tramscount
> If you want to see if you have a
> transaction or not, you can (in SQL 2005) use
> sys.dm_tran_current_transaction:
> select t,some_col, transaction_id
> from sys.dm_current_transaction, some_table t
>
> and you will see a transaction id.
I tried this:
USE tempdb
go
CREATE FUNCTION tramscount(@spid int) RETURNS int AS
BEGIN
DECLARE @cmd varchar(8000), @sql varchar(4000)
SELECT @sql = 'SELECT * INTO vovve FROM ' +
'sys.dm_tran_session_transactions ' +
'WHERE session_id = ' + ltrim(str(@spid))
SELECT @cmd = 'sqlcmd -d tempdb -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd
RETURN @@trancount
END
go
SELECT TOP 1 dbo.tramscount(@@spid), OrderID FROM Northwind..Orders
SELECT * FROM vovve
go
DROP TABLE vovve
DROP FUNCTION tramscount
No transaction in sight.
Alex Kuznetsov
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
yet the following query does return a row, and the id increments every
time:
select * from sys.dm_tran_current_transaction
Alex Kuznetsov
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Also I did this:
CREATE FUNCTION Readers.Wait10seconds()
RETURNS INT
AS
BEGIN
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d2 = dateadd(ms, 10000, getdate()), @d1 = getdate()
WHILE @d1 < @d2 BEGIN
SET @d1 = getdate()
END
RETURN 0
END
GO
SELECT Readers.Wait10seconds()
-- in another window:
select * from sys.dm_tran_active_transactions
and I do see both selects as transactions all right.
Mike C#
>> Well, the second sentence I gave you came from your Wikipedia article. My
>> original definition comes from the SQL Server-specific implementation of
>> ACID property enforcement.
>
> Where exactly from? Can you provide a url?
For what? If you're interested in SQL Server's transaction log
implementation and how it enforces atomicity and durability, BOL is an
excellent place to start. If you're interested in learning how SQL Server's
isolation levels and locking mechanisms enforce isolation, BOL is great for
that as well. If you're interested in Wikipedia, try the same page you
borrowed your initial partial definition from.
>> Run a long-running SELECT query in SSMS and cancel the query halfway
>> through. The SELECT query will return partial results.
>
> besides that, the query will fail. it is the responsibility of the
> client to discard those partial results.
You disprove your own point. In a SQL Server transaction, it is the
responsibility of the server to "discard" and roll back partial results.
That's the point of SQL transactions.
Transactions enforce atomicity and durability. The original question is was
"Is a select statement a transaction?", not "can a select statement be
included in an explicit transaction?" If you believe every SELECT statement
should be wrapped in an implicit transaction then you should submit a case
to Connect and ask why they don't wrap SELECT queries in SQL transactions.
Ask them why they feel SELECT does not need to enforce atomicity and
durability.
>> You seem to be proposing that SELECT queries, which enforce around 50% of
>> the ACID properties, are therefore wrapped in implicit SQL transactions.
>> They also cannot be committed or rolled back like real transactions. I
>> think maybe you should come up with a more appropriate and descriptive
>> terminology, maybe instead of a real "transaction", you could refer to
>> this
>> behavior as a "halfaction".
>
> Your requirements are too stringent - I don't know any RDBMS which
> implements transactions that meet your requirements.
Surprisingly, SQL Server transactions enforce atomicity and durability, and
very well I might add. I would like to know the names of the databases you
work with that don't meet these "stringent" requirements and that don't
enforce the ACID properties so that I can avoid them. Though you may have
the luxury of playing fast-and-loose with your data, any modern database
that doesn't ensure data integrity is completely useless to me.
> Because your arguments are not too convincing, let me give you a
> better argument supporting your point of view. If your run the
> following:
LOFL. You're the one who works with databases that don't enforce ACID
stringently. Not surprisingly, and especially in a multiuser environment,
the stringent nature of the ACID properties is many times the only thing
keeping your database from being totally hosed.
> SELECT c.SomeColumn, @@TRANCOUNT AS Trancount FROM data.Customers c
>
> you will get 0 in Trancount column...
Do you have any convincing arguments that support *your* point of view? So
far you've stated that ACID properties don't apply to these mysterious
implicit "transactions" that you think wrap SELECT queries, though they
apply to every other SQL transaction.
You've argued that ACID is too stringent to try to apply, and that most
databases you use don't enforce ACID very well.
You've argued that half of a definition from Wikipedia (unsourced
definition, by the way) supports your case, while discarding the second half
of the definition from the same page.
You've argued that SELECT query result sets don't reside on the server, so
therefore ACID is not applicable; though at the same time you're arguing
that SQL server essentially wastes its resources by wrapping these SELECT
queries in implicit transactions.
I'm still waiting for an answer on 'If result sets exist "outside the
RDBMS", then how does SQL "transaction" apply at all?'
Mike C#
> yet the following query does return a row, and the id increments every
> time:
>
> select * from sys.dm_tran_current_transaction
First let me apologize and admit that I'm wrong. After reading more in BOL,
I've found that SELECT queries are wrapped in READ-ONLY transactions. This
is a different type of transaction than a real DML transaction, and it
doesn't enforce the ACID properties, and it obviously serves a different
purpose from true DML transactions.
I'm still 100% interested in knowing which DBMS's you use that don't enforce
ACID, if you can provide a quick reference.
Does anyone know the purpose of READ-ONLY transactions?
Thanks
Kalen Delaney
DML stands for Data Manipulation Language, and includes SELECT, so saying 'a
real DML transaction' doesn't mean much.
(Some people think that DML means Data Modification Language, but that is
not correct. Please see http://en.wikipedia.org/wiki/DML )
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Mike C#" <x...@xyz.com> wrote in message
news:OPeTTBlg...@TK2MSFTNGP02.phx.gbl...
Mike C#
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:%23WmQOcl...@TK2MSFTNGP06.phx.gbl...
> One other misunderstanding...
>
> DML stands for Data Manipulation Language, and includes SELECT, so saying
> 'a real DML transaction' doesn't mean much.
That's interesting... in the ISO standard the only version of SELECT that's
included in DML is the single-row "SELECT ... INTO ..." variation. The
SELECT query is included in the standard under "Queries". I wonder what the
significance was in the decision to differentiate between "query" statements
and "data manipulation" statements in the standard?
FYI, 'Real DML transaction' should have been 'Read/write DML transaction'.
Mike C#
>
> Also I did this:
>
> CREATE FUNCTION Readers.Wait10seconds()
> RETURNS INT
> AS
> BEGIN
> DECLARE @d1 DATETIME, @d2 DATETIME
> SELECT @d2 = dateadd(ms, 10000, getdate()), @d1 = getdate()
> WHILE @d1 < @d2 BEGIN
> SET @d1 = getdate()
> END
> RETURN 0
> END
> GO
>
> SELECT Readers.Wait10seconds()
>
> -- in another window:
>
> select * from sys.dm_tran_active_transactions
>
> and I do see both selects as transactions all right.
Yes, you are seeing 2 READ-ONLY transactions.
Erland Sommarskog
> First let me apologize and admit that I'm wrong. After reading more in
> BOL, I've found that SELECT queries are wrapped in READ-ONLY
> transactions. This is a different type of transaction than a real DML
> transaction, and it doesn't enforce the ACID properties, and it
> obviously serves a different purpose from true DML transactions.
but a read-only transaction is not as meaningless it may seem. Sure,
with READ COMMITTED isolation it's kind of pointless. But with
REPEATABLE READ, SERIALIZABLE or SNAPSHOT it is not. In this case a
read-only transaction defines a unit under which which we do not permit
certain changes to the data we access.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Alex Kuznetsov
> "Alex Kuznetsov" <alk...@gmail.com> wrote in message
>
> news:61ae1a47-4f44-4e05...@o77g2000hsf.googlegroups.com...
>
> > yet the following query does return a row, and the id increments every
> > time:
>
> > select * from sys.dm_tran_current_transaction
>
> First let me apologize and admit that I'm wrong.
Accepted.
Alex Kuznetsov,
SQL Server MVP