Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2008 9:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
Comments posted to this topic are about the item Transaction

--www.sqlvillage.com
Post #534844
Posted Wednesday, July 16, 2008 5:04 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Excellent question! So many SQL developers don't understand the difference between isolation levels and the impact of using them!
Post #535083
Posted Wednesday, July 16, 2008 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 369, Visits: 218
It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.
Post #535180
Posted Wednesday, July 16, 2008 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #535184
Posted Wednesday, July 16, 2008 8:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
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)
Post #535264
Posted Wednesday, July 16, 2008 8:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:40 AM
Points: 280, Visits: 395
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?
Post #535279
Posted Wednesday, July 16, 2008 9:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 6, 2009 1:29 PM
Points: 2,057, 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.
Post #535344
Posted Wednesday, July 16, 2008 10:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 6, 2009 1:29 PM
Points: 2,057, 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?
Post #535408
Posted Wednesday, July 16, 2008 1:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #535530
Posted Thursday, July 17, 2008 5:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,478, Visits: 1,402
Excellent question ....


Post #535877
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse