SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction


Transaction

Author
Message
Mohan Kumar
Mohan  Kumar
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2199 Visits: 596
Comments posted to this topic are about the item Transaction

--www.sqlvillage.com
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2979 Visits: 711
Excellent question! So many SQL developers don't understand the difference between isolation levels and the impact of using them!
umailedit
umailedit
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 246
It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57905 Visits: 9730
umailedit (7/16/2008)
It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.


Somehow I doubt that Oracle defaults to dirty reads, so what does it do when one connection selects data that another connection is in the process of updating or deleting?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3259 Visits: 6497
The answer doesn't seem to be 100 percent accurate. If you look up BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/016fb05e-a702-484b-bd2a-a6eabd0d76fd.htm under SNAPSHOT ISOLATION it reads:

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data.


and a litle further down

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.


Am I missing something (again)?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
A_A_R_T
A_A_R_T
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 423
Jan Van der Eecken (7/16/2008)
The answer doesn't seem to be 100 percent accurate. If you look up BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/016fb05e-a702-484b-bd2a-a6eabd0d76fd.htm under SNAPSHOT ISOLATION it reads:

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data.


and a litle further down

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.


Am I missing something (again)?

That's exactly what I found!

Can someone explain this please?
Steven Cameron
Steven Cameron
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 215
To me it sounds like SNAPSHOT transactions issue a shared lock request so that it will wait until the rolled back transaction or database recovery has been completed. Once that is done, the lock is released. So it doesn't required it for the read transaction itself.
Steven Cameron
Steven Cameron
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 215
umailedit (7/16/2008)
It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.


Then why does Google come up with 1,380,000 results for Oracle Locking?
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5293 Visits: 3889
umailedit (7/16/2008)
It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.

Hi umailedit,

can you please elaborate a little more on why you think that pessimistic locking is primitive?
I think that both locking mechanisms (optimistic and pessimistic) have their deserved place in todays database technology. The benefit of SQL Server is that you can choose between optimistic an pessimistic locking. In oracle you can't. (But the overhead of SQL Server optimistic locking seems to be more drastic than in oracle.)

For more good comparison of the locking types mvrc, mvcc and pessimistic locking, I suggest the following reading:
http://www.ibphoenix.com/main.nfs?page=ibp_mvcc_roman
Edit: Fixed Url tags.

Best Regards,

Chris Büttner
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9791 Visits: 1407
Excellent question ....



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search