12 Replies Latest reply: Dec 15, 2009 4:06 PM by mbobak RSS

    READ UNCOMMITTED or NOLOCK on Oracle

    627145
      Hi,

      In SQL Server, I can do

      1. select * from tb_name (nolock)
      or
      2. SET TRANSACTION ISOLATION LEVEL
      READ UNCOMMITTED

      To avoid blocking due to lengthy select operation.

      Do we have something like this in Oracle?

      Thanks
      Claire
        • 1. Re: READ UNCOMMITTED or NOLOCK on Oracle
          ben23
          Nope, no dirty reads in Oracle.

          No blocking in Oracle either.

          But, ahem, your snapshot might get a bit too old.
          • 2. Re: READ UNCOMMITTED or NOLOCK on Oracle
            sgalaxy
            The Oracle Db allows :
            1) readers of the data not to be blocked by writers of the data
            2) writers of the data not to be blocked by readers of the data

            You can find info reading the following...
            http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#insertedID3

            Greetings...
            Sim
            • 3. Re: READ UNCOMMITTED or NOLOCK on Oracle
              Aman....
              That's one of my favourite topics.
              In oracle select doesn't block anything except when we want it to. And Oracle the isolation level read committed is preserved by default that means as mentioned by Sim, in oracle, Readers don't wait for writes and writers don't wait for readers.
              Please read the link that is mentioned by Sim.And just a suggestion, if you are in Oracle world, think from oracle point of view. I meet lot of people every week who wants x feature implemented in database abc in Oracle.Well it may be there , may not be.
              Have fun with Oracle.
              Aman....
              • 4. Re: READ UNCOMMITTED or NOLOCK on Oracle
                sgalaxy
                Yes... exactly these characteristics permit Oracle DB to be the more scalable DB in the world....

                Greetings...
                Sim
                • 5. Re: READ UNCOMMITTED or NOLOCK on Oracle
                  Aman....
                  100% and also no lock escalation as like other db's.
                  Aman....
                  • 6. Re: READ UNCOMMITTED or NOLOCK on Oracle
                    Basil
                    yes, we all know that Oracle has unlockable read from rollback segments using SCN, but as DBA I WANT to see uncommited data of some of my hanged applications to know what they are doing before they commit.

                    And how can I do it?
                    • 7. Re: READ UNCOMMITTED or NOLOCK on Oracle
                      289595
                      You may want to read:

                      http://laurentschneider.com/wordpress/2008/08/read-uncommitted.html
                      • 8. Re: READ UNCOMMITTED or NOLOCK on Oracle
                        Basil
                        Not helpful...
                        If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.
                        • 9. Re: READ UNCOMMITTED or NOLOCK on Oracle
                          289595
                          Actually, the link was to support that you won't be able to do so.....

                          The last line in the link: 'But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

                          '
                          • 10. Re: READ UNCOMMITTED or NOLOCK on Oracle
                            Hans Forbrich
                            Basil wrote:
                            Not helpful...
                            If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.
                            You are right. Oracle does not bother with such things, as they are rarely needed in Oracle in a well designed application.

                            Unfortunately many developers are not aware of things like DBMS_APPLICATION_INFO which would help weird monitor situations.
                            • 11. Re: READ UNCOMMITTED or NOLOCK on Oracle
                              sb92075
                              And how can I do it?
                              You can see SQL involved by starting with code example below
                                1  SELECT USERNAME, ss.sql_text
                                2  FROM   v$session vv, v$sql ss
                                3  WHERE  vv.TADDR IS NOT NULL
                                4*  and   vv.prev_sql_id = ss.sql_id
                              SQL> /
                              
                              USERNAME
                              ------------------------------
                              SQL_TEXT
                              --------------------------------------------------------------------------------
                              DBADMIN
                              insert into ora4063 values (1,2)
                              I issued the INSERT in a different terminal window & did not COMMIT;
                              • 12. Re: READ UNCOMMITTED or NOLOCK on Oracle
                                mbobak
                                Hi Claire,

                                In addition to everyone else's comments, I would add this:

                                It's important to understand that when you come from a different database system, such as SQL Server, for example, that concepts may not map in a 1 to 1 manner. It's better to ask about the problem you're trying to solve, rather than ask "How do I do this particular task in Oracle?" Problems in Oracle may map to a completely different set of tasks than in SQL Server.

                                If you had started out by asking "How can I monitor in-process, uncommitted data, in order to understand what's happening with my application when it gets hung?", you may have gotten a pointer to DBMS_APPLICATION_INFO, or DBMS_PIPE or UTL_FILE much more quickly.

                                Hope that helps,

                                -Mark