Hi Jonathan,
I understand that , if we have and index in the column id, the first and
the third line will lock the line, according the bind variable.
it is running in session S1
update t1 set n1 = 99 where id = :b1;
update t2 set a1 = 66
update t1 set n4 = 33 where id1 = :b2
Imagine another session (S2) using the same ID for update , it will be
stopped until the first session apply the commit.
This is a basic concept of the lock.
The issue is that when we have millions of updates, inserts, and deletes in
the production environment, the sql that triggered the transaction may no
longer be in the SGA, even though it triggered a transaction.
Regards
Eriovaldo
Em seg, 29 de out de 2018 às 08:45, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> escreveu:
Eriovaldo,
I am surprised that anyone even bothered to create that enhancement
request, but someone show have marked it is "ignore" by now.
Consider the following transaction:
update t1 set n1 = 99 where id = :b1;
update t2 set a1 = 66
update t1 set n4 = 33 where id1 = :b2
Your ER asked for the sql_id of the first statement to be stored in
v$transaction.
How are you, while trouble-shooting, going to behave it it's actually the
third statement that is blocking you ?
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
Sent: 29 October 2018 11:39:03
To: neil_chandler@xxxxxxxxxxx
Cc: Mladen Gogala; ORACLE-L
Subject: Re: How to get the statment sql that caused the lock
Hi Gogala,
Thanks for your comments.
When I wrote "query" I want mean : sql statements executing : INSERT,
UPDATE, DELETE, TRUNCATE and LOCK (select for update).
I agree, we can get the objetct locked, and for it I used this query:
select
sb.sid sid_blocker
,sb.inst_id inst_id_blocker
,sb.event
,substr(sb.program,1,25) program
,o.OBJECT_NAME
,CASE WHEN
o.object_type = 'TABLE' THEN
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID,
sb.ROW_WAIT_FILE#, sb.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# )
ELSE
NULL
END rowid_bloocked
from gv$session s
,gv$session sb
,dba_objects o
where s.seconds_in_wait > 60
and s.BLOCKING_SESSION = sb.sid
and s.BLOCKING_INSTANCE = sb.INST_ID
and sb.ROW_WAIT_OBJ# = o.OBJECT_ID;
My purpose is to retrieve the sql_id related to the transaction line in
the view v$transaction that is responsible for the execution of the :
INSERT, UPDATE, DELETE ... in order to show it to the developer and
validate the application if need to add commit in the code, if does not
exists.
I received a message from a member of the group , like this :
I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for
transaction to be added to v$transaction) and they created an enhancement
request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED
THE TRANSACTION
But two years have passed. They did nothing about it.
This is what I am locking for.
Thanks and Regards
Eriovaldo
Em seg, 29 de out de 2018 às 05:51, Neil Chandler <
neil_chandler@xxxxxxxxxxx<mailto:neil_chandler@xxxxxxxxxxx>> escreveu:
Queries can lock tables:
SELECT... FOR UPDATE;
I find this is a common cause of blocking and is easy to overlook as you
might miss the FOR UPDATE.
Table DDL can also lock the resource by dropping a TM lock on there.
Neil.
sent from my phone
On 29 Oct 2018, at 03:36, Mladen Gogala <gogala.mladen@xxxxxxxxx<mailto:
gogala.mladen@xxxxxxxxx>> 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
