Re: How to get the statment sql that caused the lock

“Queries do not lock tables, unless the queries are distributed, as in "query over a database link". For everything else, queries to not lock tables.”

Maybe I’m misunderstanding, but a select over a DB link does not lock the table/row in any way. You do start a transaction and acquire a DX type lock but that does not effect the table.

A select statement will put a lock on the rows selected when the ‘for update’ clause is used. These can be very common for some applications and not used at all in others.

I enjoyed the anecdote about /*+append*/, note that thankfully Oracle is sensible enough for the hint not to apply for insert..values, only insert..select statements. You would use insert_values hint if you really wanted to use direct path insert with insert..values, this could be legitimate in cases using huge array binds but I’d be skepticle.

Regards,
Andy

On Mon, 29 Oct 2018 at 02:42, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Eriovaldo!
>
> Queries do not lock tables, unless the queries are distributed, as in
> "query over a database link". For everything else, queries to not lock
> tables.
>
> Second, the fact that the sessions are idle is irrelevant. If I execute
> "LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will
> be idle. It will also keep the lock until the transaction ends. Locks are
> caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the
> purpose of this consideration, we will consider MERGE to be an update &
> insert combination.
>
> So, if you have a SID, you should be able to find out what objects does
> the session have locked and in what mode. Sometimes, that can be a load of
> fun. While I was a DBA, I worked with a developer who learned that /*+
> APPEND */ hint for inserts "makes things go faster". So she has put the /*+
> APPEND */ hint to each and every insert statement in her multi-user web
> application. For some inexplicable reason, things did not go faster, but I
> did have some fun.
>
> - Idle sessions can hold locks, for a very long time. That was the
> primary reason for inventing resource limits.
> - Queries, except in very rare cases, DO NOT LOCK anything.
> - V$LOCK will tell what objects are locked and in what mode. Unless
> you are using 18.3 or newer, you cannot kill a statement (actually, not
> true: you can kill the current statement by using kill -URG, but very few
> people know that and even fewer do that).
> - Once you figure out that an idle session is blocking you by holding
> a lock, you kill it without further ado and ask the application developer
> to fix the bug. There is absolutely no reason whatsoever for an idle
> session to hold locks. Locks are means for preserving consistency. You want
> to prevent someone else from modifying a critical resource until your
> transaction is finished. You don't hold locks on resources until an upgrade
> to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
> - Just for completeness, the /*+ APPEND */ hint causes the insert to
> allocate a batch of empty blocks below the high water mark and effectively
> extend the table. That requires an exclusive lock on the table. And
> exclusive locks can be bad for concurrency.
>
> On 10/27/18 6:49 PM, Eriovaldo Andrietta wrote:
>
> Hello,
>
> I using Oracle 12c R2.
> I have this query that shows blocked sid:
>
> Query 1 :
> select * from gv$lock where sid in
> (select sid from gv$session where blocking_session is not null);
>
> and here , the query shows the blocker sid. These sessions are IDLE.
> Query 2
> select * from gv$lock where sid in
> (select blocking_session from gv$session where blocking_session is not
> null);
>
> My doubt is : what is the query that caused the lock that ran in the
> blocker sessions?
>
> I tried the query below, but did not get any query using the locked table.
>
> select * from
> (
> select sql_id
> ,to_char(last_active_time,'dd-hh24:mi:ss') last_active
> ,executions
> ,elapsed_time/1000000 elap_tot
> ,decode(executions,0,elapsed_time,(elapsed_time/executions))/1000000
> elap_exec
> ,decode(executions,0,disk_reads,(disk_reads/executions)) disk_exec
> ,decode(executions,0,buffer_gets,(buffer_gets/executions))
> buffer_exec
> ,tot
> ,sql_text
> from (select s.sql_id
> ,substr(s.sql_text,1,225) sql_text
> ,max(last_active_time) last_active_time
> ,sum(executions) executions
> ,sum(elapsed_time) elapsed_time
> ,sum(disk_reads) disk_reads
> ,sum(buffer_gets) buffer_gets
> ,count(*) tot
> from gv$sql s
> ,gv$open_cursor o
> where s.inst_id = o.inst_id
> and s.sql_id = o.sql_id
> and o.user_name = s.parsing_schema_name
> and o.sid=&vSid
> and o.inst_id = nvl('&vInstId',1)
> and s.parsing_schema_name <> 'SYS'
> group by s.sql_id
> ,substr(s.sql_text,1,225))
> order by to_char(last_active_time,'yyyymmddhh24mi'), elap_exec
> )
> where elap_exec > &vElap;
>
> What is the way to find out the query that caused the lock (in sessions
> showed in the Query 2 above)
>
> Regards
> Eriovaldo
>
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l