Re: Lock requests queued?
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 20 Jun 2012 07:51:26 -0700 (PDT)
Message-ID: <79df60fc-a294-4ae2-b116-61005fb1a745_at_googlegroups.com>
On Tuesday, June 19, 2012 7:05:54 PM UTC-4, joel garry wrote:
> On Jun 19, 10:10 am, Frank Langelage <fr..._at_lafr.de> wrote:
> > To serialize some kind of data modifications our application uses an
> > exclusive table lock (lock table %s in exclusive mode).
> > The process retrieves some data, then acquires the lock and does the
> > modifications. Then work is committed and with that the lock is released.
> > After process 1 has acquired the lock at time t1, other processes might
> > come and require the lock. Lets say process 2 at t2 and process 3 at t3.
> > Is it guaranteed, that the process 2 will be the next to get the lock
> > because it was first who tried to get it, so waiting for the longest
> > time? Or maybe better: are the lock requests queued?
> > Or is it more or less random, depending which of the Oracle
> > processes/threads gets CPU time?
> >
> > (Oracle server version 10.2 or 11.2 on Windows, IBM AIX and Solaris SPARC).
>
> I'm not real sure, but I think it will be random. I believe if you are
> overriding the default locking, you are responsible for making it do
> what you want. Oracle uses lock escalation, so you really have to
> watch what is happening. There are various examples around on how to
> check what locks are happening, the first thing I googled upon was
> this (note the TM discussions): https://forums.oracle.com/forums/thread.jspa?threadID=638343
> And of course, http://jonathanlewis.wordpress.com/category/oracle/locks/
>
> jg
> --
> _at_home.com is bogus.
> “I’m absolutely personally responsible,” Jamie Dimon told the Senate
> Banking Committee in Washington, D.C.
> So now "personal responsibility" means "make others pay."
Date: Wed, 20 Jun 2012 07:51:26 -0700 (PDT)
Message-ID: <79df60fc-a294-4ae2-b116-61005fb1a745_at_googlegroups.com>
On Tuesday, June 19, 2012 7:05:54 PM UTC-4, joel garry wrote:
> On Jun 19, 10:10 am, Frank Langelage <fr..._at_lafr.de> wrote:
> > To serialize some kind of data modifications our application uses an
> > exclusive table lock (lock table %s in exclusive mode).
> > The process retrieves some data, then acquires the lock and does the
> > modifications. Then work is committed and with that the lock is released.
> > After process 1 has acquired the lock at time t1, other processes might
> > come and require the lock. Lets say process 2 at t2 and process 3 at t3.
> > Is it guaranteed, that the process 2 will be the next to get the lock
> > because it was first who tried to get it, so waiting for the longest
> > time? Or maybe better: are the lock requests queued?
> > Or is it more or less random, depending which of the Oracle
> > processes/threads gets CPU time?
> >
> > (Oracle server version 10.2 or 11.2 on Windows, IBM AIX and Solaris SPARC).
>
> I'm not real sure, but I think it will be random. I believe if you are
> overriding the default locking, you are responsible for making it do
> what you want. Oracle uses lock escalation, so you really have to
> watch what is happening. There are various examples around on how to
> check what locks are happening, the first thing I googled upon was
> this (note the TM discussions): https://forums.oracle.com/forums/thread.jspa?threadID=638343
> And of course, http://jonathanlewis.wordpress.com/category/oracle/locks/
>
> jg
> --
> _at_home.com is bogus.
> “I’m absolutely personally responsible,” Jamie Dimon told the Senate
> Banking Committee in Washington, D.C.
> So now "personal responsibility" means "make others pay."
"Oracle uses lock escalation" Since when? In SQL Server if you take too many row locks in a block SQL Server will upgrade the row locks to a page lock and if you get too many page locks in the table the page locks are upgraded to a table lock, but Oracle does not do this. In fact the modern versions of SQL Server pretty avoid doing this.
HTH -- Mark D Powell -- Received on Wed Jun 20 2012 - 09:51:26 CDT