Posted in Oracle DBMS, RDBMS, SQL, Sybase

Row level Locking

In the Sybase vs Oracle discussion there is always one issue that Oracle used to win. Row level locking. In the early days of Sybase, page level locking was the lowest level of granularity that locks attained. Much of this was due to a conscious choice of increasing the performance of the transaction, rather than ensuring unique row locking in Sybase. After row level locking was added to Sybase at version 11, this was proved to be a false ‘feature’ as most Sybase installations rarely implemented it.

In retrospect most issues relating to row level being a requirement can be traced back to badly implemented transaction clients. which makes this even more evident, is that in many cases, when the client transaction were ‘repaired’ to implement true ACID transactions, the Oracle databases performed better, and the row level locking issues vanished as a requirement.

UPDATE:

One other difference between Oracle and Sybase which doesn’t get much visibility is the business of isolation levels. Oracle allows the ‘dirty’ read and Sybase does not. This is changeable in both databases, but Oracle used this to their advantage in the locking wars as shared, and shared intent locking did not cause the same kind of issues like the ones you would encounter in Sybase.

Advertisements

Author:

With 40 years experience in software development, systems design and engineering and IT operations, and Infrastructure Architecture issues. I am versed in multiple programming languages, Operating Systems and RDBMS, I have work experience ranging from microcomputers and PC’s to multiprocessor mid range Unix systems and clusters. I have experience with both wireless and wired network protocols and mediums. And I've help migrate systems into the Amazon EC2 Cloud from self hosted configurations. I collect old working computers, I'm a published Astro-photographer, I tutor, and teach almost every subject I am knowledgeable in. I have had one internet email or another since 1991. I developed Gopher sites prior to the formation of HTTP/HTML and a few websites since then. I wrote my first 'database' on a DEC PDP-11 for the DECUS Library in 1984. Specialties I specialize in Database systems, and am familiar with almost all types of RDBMS and ISAM systems short of Mainframes. I habitually reverse engineer and document everything I touch.

8 thoughts on “Row level Locking

  1. Someone wrote me and ask if I didn’t make a mistake an mean Sybase database performed better and I responded like this:

    Actually I did mean Oracle, transactions done correctly help Oracle almost as much as Sybase. In reality Oracle, with good clients really doesn’t need row level locking at all. Hence row level locking in well behaved transactions is a meaningless feature. (Optimistic locking and timestamp issues aside)

    But I agreed, that I could have made it more clear.

  2. I just found your blog through a search on sybase + wordpress.
    I have a similar background to you 20 years in IT. Most of it with Sybase these days , plenty of Oracle in the old days. I agree with a lot of your points on the Sybase / Oracle debate. I still prefer Oracle though!

    One thing that really bugs me in Sybase is the lack of O/S authenticated logins. In Oracle you just use sqlplus /
    and your “OPS$” logins remove all the complexity required with having userids and passwords for the database as well as the O/S.

    Particulary with batch programs and SOX compliance for Sybase dbs messing around keeping track of the batch userids and logins a real PITA.

  3. Sybase is a true client/server database with it’s origins AS an operating system, hence it’s native user login methods. These methods can be configured in much the same way as any OS. At AT&T we had servers with only a few dozen OS logins, and 15,000 user logins to the databases. Try and admin that many users at the OS level and you will really have fun. However we used Kerberos to manage them, but you can use LDAP, or external OS authentications (now) or a number of other security profiles.

  4. Hi,
    although it is a old post I’m really astonished to read that Oracle supports Dirty Reads and Sybase not. Well, it’s completely contrary. Posts like this one are the cause why there is such poisend competence between Sybase and Oracle guys.

    Default mode of Sybase isolation level ist 1, this corresponds to read committed. The same level as Oracle. Sybase default mode is unchained mode – Oracle does not even know such things, it handles everything in what Sybase calls chained mode. Does Oracle have locking problems? No – it has a Multiversion Consistency Models and this leads to applications where readers are never blocked! NEVER.
    I’ve been migrating different application from Sybase to Oracle and vice versa and let me tell you that Sybase imposes me lots of additional work for sorrounding effects coming out of this completely poorly conceived isolation level handling of Sybase. Furthermore it is often necessary to split business transactions in smaller ones because of this limitations. If you don’t handle this you can get logically correupted data. Things you don’t experience in Oracle.

    Both systems have pros and cons – but when it comes to compare features – please don’t rely on half-thruths because it discualifies any writer. Just a question: haven you tried to run ‘set transaction isolation level read uncomitted’ on Oracle? On Sybase ‘set transaction isolation level 0’ does. Tell me if I’m wrong!

    Felix

    1. Felix, you are correct Sybase uses ‘read committed’ however if the record/page is open for update on a another thread it will not read it until the thread commits the change. hence the column in sp_who that indicates the blocking thread, also known as a ‘live lock’. Oracle will read a record that is open by another thread/process without requiring it to commit the operation.

      “(Oracle) readers are never blocked!”, it’s called a ‘dirty read’ as the reader is unaware of a potential change to the data it is reading. it’s the ‘past value’ that Oracle selects, allowing two process threads to potentially change and alter each others records incorrectly.

      Sybase DOES make you think about how you do transactions correctly, AND if the process works for Sybase, it DOES improve how Oracle performs as well, for the same schema. No sloppy processes for Sybase are allowed. It also makes them more ACID compliant.

      And yes this is an old issue/posting and most Sybase databases are mixtures of page and row level locking tables, built and changed as required.

  5. Hi rwjordan

    you got me – I’m sure you’re kidding, aren’t you? If not … I suggest that you take a closer look on database theory, nevertheless which one. And before writing things that are demonstrably wrong make just a few little tests and you’ll see.

    With the words of a really respectable person: Just because it’s written doesn’t mean it is true!

    Felix

    1. I’m sorry you feel that way, I do have a lot to learn, I only wrote my first ‘database’ for PDP-11’s in 1984 and had it accepted in the DECUS program library.

      There is nothing wrong with theory, but in the words of Chris Date, the (relational) theory is perfect, only the vendors fail to implement the Theory.

  6. Coming to “dirty read” – depends on how you interpret it. According to RWJORDAN, this is potential “bad data”, which could be subjected to change by another user and Oracle WILL ALLOW user to read such data.
    Felix is right if “dirty read” is interpreted as “already written but uncommitted yet” and Oracle DOES NOT allow user to read such data.
    From my VAX/RDB background I’d interpret “dirty read” as RWJORDAN.
    Please let me know if I am wrong. Thanks.

Leave a Reply to Atanu Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s