Transaction

Question

Transaction

Mohan Kumar

SSCertifiable

Points: 5365
More actions
July 15, 2008 at 9:35 pm

#381718

Comments posted to this topic are about the item Transaction
[font="Verdana"]--www.sqlvillage.com[/size][/font]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 1

Excellent question! So many SQL developers don't understand the difference between isolation levels and the impact of using them!

umailedit SSCrazy Points: 2087 More actions · Answer 2

It seems primitive to require locks for any read operation. In Oracle I think readers don't lock/block anything.

GSquared SSC Guru Points: 260824 More actions · Answer 3

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 SSCrazy Eights Points: 9029 More actions · Answer 4

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)[/url]

A_A_R_T SSC Eights! Points: 900 More actions · Answer 5

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 SSCrazy Points: 2903 More actions · Answer 6

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 SSCrazy Points: 2903 More actions · Answer 7

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 SSChampion Points: 13729 More actions · Answer 8

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 SSC-Insane Points: 24681 More actions · Answer 9

Anipaul

SSC-Insane

Points: 24681

July 17, 2008 at 5:59 am

#844302

Excellent question ....

umailedit SSCrazy Points: 2087 More actions · Answer 10

Even if Oracle does lock, I still think that this locking concept itself is primitive. Queuing is much better than locking. Both for reading AND WRITING. What do you say?