8 Replies Latest reply: Nov 13, 2012 3:56 AM by Jonathan Lewis RSS

    Locks on temporary tables

    Exor
      Hello,

      Does anyone have any background information on temporary tables internals? Is the locking mechanism any different from heap tables?
      I am asking because I am noticing that row exclusive locks are kept on global temporary tables on commit preserve until the session disconnects. I don't understand why exclusive row locks are kept across commit statements. Can someone explain this?

      Thank you,
        • 1. Re: Locks on temporary tables
          sb92075
          Exor wrote:
          Hello,

          Does anyone have any background information on temporary tables internals? Is the locking mechanism any different from heap tables?
          I am asking because I am noticing that row exclusive locks are kept on global temporary tables on commit preserve until the session disconnects. I don't understand why exclusive row locks are kept across commit statements. Can someone explain this?

          Thank you,
          Your observation is not relevant; since only single session can access the data anyways.
          • 2. Re: Locks on temporary tables
            Exor
            Hello Sb,

            I am fully aware that only single sessions can access the data and that these locks can never become blocking or blocked locks. I am still interested in finding out why is this happening.

            Thank you,
            • 3. Re: Locks on temporary tables
              rp0428
              >
              am fully aware that only single sessions can access the data and that these locks can never become blocking or blocked locks. I am still interested in finding out why is this happening.
              >
              Since you accept the premise that sb92075 gave, and that you state above, I'm surprised you didn't ask why Oracle bothers to place the locks to begin with. ;)

              The locks exist in the blocks that contain the data. To clear the locks Oracle would have to revisit every block. That can be a very expensive operation - even equivalent to a full table scan. Since Oracle knows that no other session can access the data it also knows that it can skip that potentially expensive operation.

              In short, there is NOTHING to be gained by releasing the locks but a lot to lose.

              As for the answer to why Oracle bothers to place the locks? Temporary tables have no rows when the session starts and if any rows are created the locks can be placed at the same time the data is added to the block uses Oracles normal code path.

              In short, there is NO COST to add the locks using the normal code path. So why bother creating a new code path to avoid adding locks?
              • 4. Re: Locks on temporary tables
                Exor
                Thank you very much,

                This is exactly what I was looking for. I wasn't really trying to fix or change anything, I just wanted to understand.

                Thanks again,
                • 5. Re: Locks on temporary tables
                  rp0428
                  Please mark the question ANSWERED and help keep the forum clean.
                  • 6. Re: Locks on temporary tables
                    Jonathan Lewis
                    Exor wrote:

                    Does anyone have any background information on temporary tables internals? Is the locking mechanism any different from heap tables?
                    Yes to part 1, and No to part 2.
                    I am asking because I am noticing that row exclusive locks are kept on global temporary tables on commit preserve until the session disconnects. I don't understand why exclusive row locks are kept across commit statements. Can someone explain this?
                    I know that you've accepted the answer that rp0428 gave - but what are you calling a "row exclusive lock" on a temporary table, and how are you seeing them after a commit ?

                    Regards
                    Jonathan Lewis

                    Edited by: Jonathan Lewis on Nov 3, 2012 3:39 PM
                    • 7. Re: Locks on temporary tables
                      Exor
                      Hello rp0428,

                      Thank you very much for your help, I will mark the question as answered and your answer as correct soon. I just want to answer to the last question. Will do that very soon.

                      Thanks again,
                      • 8. Re: Locks on temporary tables
                        Jonathan Lewis
                        Exor wrote:
                        Hello rp0428,

                        Thank you very much for your help, I will mark the question as answered and your answer as correct soon. I just want to answer to the last question. Will do that very soon.
                        It would be nice if you answered my question. After all, the relevance of the answer rp0428 has given you is dependent on your oriiginal description being correct, and since the term "row exclusive locks" is used by some people to mean TX locks in mode 6 and by others to mean TM locks in mode 3 (and neither persists after a commit) then your original description is wrong and his answer is irrelevant OR you've been doing symbolic block dumps of the temporary tablespace and have used the wrong term to describe lock bytes.

                        Regards
                        Jonathan Lewis