Re: Locking in SQL Server and Oracle
From: Galen Boyer (galenboyerdev_at_hotpop.com)
Date: 02/17/05
- Next message: David Gugick: "Re: EM not see all users from PC but I do on the server"
- Previous message: Mark Bruce: "BUG Enterprise Manager resets XACT_ABORT 'user options'"
- In reply to: Jeffrey Roughgarden: "Locking in SQL Server and Oracle"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 17 Feb 2005 18:03:37 -0500
"=?Utf-8?B?SmVmZnJleSBSb3VnaGdhcmRlbg==?=" <JeffreyRoughgarden@discussions.microsoft.com> writes:
> I am an MCDBA and MCSD in charge of several large PeopleSoft databases. I
> recently attended a 5-day Oracle 9i DBA Fundamentals 1 class to ascertain how
> hard it would be to convert to Oracle during our next PeopleSoft upgrade. I
> had thought that since databases are databases, tables are tables, and SQL is
> an ANSI standard, it really would not be that different doing Oracle. Well, I
> was wrong. Oracle is so dramatically different from SQL Server (and awkward
> and complex)
It sure does take some learn'n, thats for sure.
> that we decided to carry on with SQL Server.
>
> That said, I was impressed by one feature of Oracle, namely the fact that it
> uses row-level locks exclusively. Clearly, this reduces the liklihood of
> blocking and deadlocking significantly.
Actually, you can predict exactly when deadlocks will occur, and
therefore you can code for that, ie, you can code so they never
happen. In Oracle, if there is a deadlock the tracefile will actually
have in the error, "This is not an Oracle bug, you have a bug in your
code" and they are 100% correct. You should never have to have
production code which checks if a deadlock has occured. You should
never have them in Production. But, then again, you might still want
to be careful cause we all author bugs in our code, but, well, you get
the picture.
But, in my opinion, the main difference is that in Oracle, readers
don't block writers and writers don't block readers. You never hear
the complaint "My query is stalling because somebody is updating a row
I'm trying to read". You also never ever hear, "Turn on isolation
level Read Uncommitted". Oracle doesn't even support that level. You
cannot, ever, read an uncommitted row, unless your session is the one
writing the row.
> I had thought that Microsoft does escalated locks because it was
> infeasible to do row locks, but apparently not. Oracle runs as well
> as SQL Server in spite of the overhead it incurrs managing more
> locks.
Oracle doesn't really have the concept of locks as SQLServer does.
SQLServer knows a lock as a resource and therefore they are scarce.
In Oracle, they don't have to be "asked" for, Oracle just "flags" the
row when an dml operation occurs.
> I am curious why Microsoft does not adopt row-locking exclusively now that
> Oracle has shown it can be done.
Its at the core of the code, I'm sure. To change something at the
low-level of the codebase? Hm... Thats a tall task, for sure.
-- Galen Boyer
- Next message: David Gugick: "Re: EM not see all users from PC but I do on the server"
- Previous message: Mark Bruce: "BUG Enterprise Manager resets XACT_ABORT 'user options'"
- In reply to: Jeffrey Roughgarden: "Locking in SQL Server and Oracle"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
