Prev: Why did TRAN COMMIT?
Next: Theoretical question about index fragmentation in filegroup partitions
From: RS on 22 Feb 2010 15:42
I am trying to figure out how locking works and need some help in
understanding the concept. For instance, there is a delete statement
in a transaction that has not yet been committed. I would like to read
this data before it is committed. How do I go about reading this data.
I've tried reading this using NoLock but that returns 0 data as well.
Can I use another command to retrieve this data? I've tried adding Set
Transactions Isolation Level to Read Uncommitted but that does not
help either. Any help will be greatly appreciated.

SET TRANSACTION ISOLATION LEVEL READ uncommitted
select * from test.dbo.Employees -- returns 50 rows
begin transaction
delete test.dbo.Employees
select * from test.dbo.Employees -- returns 0 rows
select * from test.dbo.Employees with (nolock) -- returns 0 rows
rollback transaction
select * from test.dbo.Employees -- returns 50 rows
From: Tony Rogerson on 22 Feb 2010 16:00
A delete is a delete, if you are not seeing the rows that's because they've
been deleted - in the transaction and when reading the uncommitted delete in
a sperate connection. Its just that while you are still in the transaction
you can roll it back and the rows will reappear (reapplied from the
transaction log for want of a less detailed technical explanation :) ).

What you trying to do? Not sure why you want to see the deleted rows.

Tony.



"RS" <rutvijrshah(a)gmail.com> wrote in message
news:cc96366c-7e4a-46df-861e-113e1bb54a94(a)l26g2000yqd.googlegroups.com...
> I am trying to figure out how locking works and need some help in
> understanding the concept. For instance, there is a delete statement
> in a transaction that has not yet been committed. I would like to read
> this data before it is committed. How do I go about reading this data.
> I've tried reading this using NoLock but that returns 0 data as well.
> Can I use another command to retrieve this data? I've tried adding Set
> Transactions Isolation Level to Read Uncommitted but that does not
> help either. Any help will be greatly appreciated.
>
> SET TRANSACTION ISOLATION LEVEL READ uncommitted
> select * from test.dbo.Employees -- returns 50 rows
> begin transaction
> delete test.dbo.Employees
> select * from test.dbo.Employees -- returns 0 rows
> select * from test.dbo.Employees with (nolock) -- returns 0 rows
> rollback transaction
> select * from test.dbo.Employees -- returns 50 rows

From: Jay on 22 Feb 2010 16:49
You asked how locking words, gave an example of a transaction, then tried to
use dirty reads (NOLOCK & READ UNCOMMITTED).

While they are interrelated, they are all different.

Locking

Comes in two flavors: shared and exclusive
(yea, yea there are the intent variety)
Applied on a row, page, or table.

Transactions

Allows you to execute commands in a batch, or not at all.

NOLOCK

Allows you to select data that is in the table even though there may be
an exclusive lock on the data.

If you use NOLOCK/READ UNCOMMITTED, you will get the data as it currently
resides in the table, no matter where in a transaction it may be, if the
locks are released.

You will get the data as it CURRENTLY IS. If something in a transaction
causes it to roll back (as in your delete example), then the rows that
weren't there when you did the select, will reappear.

Does this help?

"RS" <rutvijrshah(a)gmail.com> wrote in message
news:cc96366c-7e4a-46df-861e-113e1bb54a94(a)l26g2000yqd.googlegroups.com...
>I am trying to figure out how locking works and need some help in
> understanding the concept. For instance, there is a delete statement
> in a transaction that has not yet been committed. I would like to read
> this data before it is committed. How do I go about reading this data.
> I've tried reading this using NoLock but that returns 0 data as well.
> Can I use another command to retrieve this data? I've tried adding Set
> Transactions Isolation Level to Read Uncommitted but that does not
> help either. Any help will be greatly appreciated.
>
> SET TRANSACTION ISOLATION LEVEL READ uncommitted
> select * from test.dbo.Employees -- returns 50 rows
> begin transaction
> delete test.dbo.Employees
> select * from test.dbo.Employees -- returns 0 rows
> select * from test.dbo.Employees with (nolock) -- returns 0 rows
> rollback transaction
> select * from test.dbo.Employees -- returns 50 rows


 | 
Pages: 1
Prev: Why did TRAN COMMIT?
Next: Theoretical question about index fragmentation in filegroup partitions