This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 23, 2013 12:23 AM by Nicolas.Gasparotto RSS

Oracle 11g :SELECT query blocked..??

user12075620 Newbie
Currently Being Moderated
Hi Experts,

could you please explain why the below SQL query is blocked?

SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=469 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1

SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=367 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1


I am scratching my head without any solution when I had a look at the db today. Thanks in advance for your help.

Regards,
Boris

Edited by: user12075620 on Dec 4, 2012 8:58 AM
  • 1. Re: Oracle 11g :SELECT query blocked..??
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    where did you get the information about the blocked session?

    Best regards,
    Nikolay
  • 2. Re: Oracle 11g :SELECT query blocked..??
    AlbertoFaenza Expert
    Currently Being Moderated
    user12075620 wrote:
    Hi Experts,

    could you please explain why the below SQL query is blocked?

    SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=469 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1

    SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=367 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1


    I am scratching my head without any solution when I had a look at the db today. Thanks in advance for your help.

    Regards,
    Boris

    Edited by: user12075620 on Dec 4, 2012 8:58 AM
    Hi,

    are you sure that SELECT 1 FROM DUAL is the guilty statement? Or maybe in the same session there are previous uncommitted transactions?
    Have you tried committing in that session and running again SELECT 1 FROM DUAL?

    If you are on 11g Release 2 I suggest to read Monitoring locks.

    Check also script utllockt.sql:
    The utllockt.sql script displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. The location of this script file is operating system dependent.
    Regards.
    Al
  • 3. Re: Oracle 11g :SELECT query blocked..??
    861120 Explorer
    Currently Being Moderated
    This is not make sense ...

    Please post the result from theses queries when the select is blocked.

    1.
    select a.session_id blocking_sid, b.session_id blocked_sid, lock_type from
    (
         select session_id, lock_id1, lock_id2, lock_type
         from dba_lock where BLOCKING_OTHERS = 'Blocking') a,
         (select session_id, lock_id1, lock_id2 from dba_lock where BLOCKING_OTHERS = 'Not Blocking') b
    where a.lock_id1 = b.lock_id1
    and a.lock_id2 = b.lock_id2;

    2.
    select BLOCKING_SESSION,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid = { sid blockers from the query 1 }

    3. @?/rdbms/admin/utllockt
  • 4. Re: Oracle 11g :SELECT query blocked..??
    user12075620 Newbie
    Currently Being Moderated
    Thanks for all your suggestions and comments.. Here is the query used

    SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
    || ' User '
    ||s1.username
    || '@'
    || s1.machine
    || ' ( SID= '
    || s1.sid
    || ' ) with the statement: '
    || sqlt2.sql_text
    ||' is blocking the SQL statement on '
    || s2.username
    || '@'
    || s2.machine
    || ' ( SID='
    || s2.sid
    || ' ) blocked SQL -> '
    ||sqlt1.sql_text AS blocking_status
    FROM Gv$lock l1,
    Gv$session s1 ,
    Gv$lock l2 ,
    Gv$session s2 ,
    Gv$sql sqlt1 ,
    Gv$sql sqlt2
    WHERE s1.sid =l1.sid
    AND s2.sid =l2.sid
    AND sqlt1.sql_id= s2.sql_id
    AND sqlt2.sql_id= s1.prev_sql_id
    AND l1.BLOCK =1
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2
    /


    fyi - SELECT 1 FROM DUAL is coming weblogic and is aimed to test the db connectivity.
  • 5. Re: Oracle 11g :SELECT query blocked..??
    Justin Cave Oracle ACE
    Currently Being Moderated
    That query isn't telling you what the string purports to tell you.

    If session 1 acquires a lock (say, it updates the KING row in the EMP table), it can go off and execute any number of other queries in the same session. It will still hold the lock until it commits (or rolls back) its transaction. Now, session 2 comes along and tries to update the same KING row in the same EMP table and blocks. Session 1 is still off issuing completely unrelated SQL statements while session 2 is blocked. The query you posted tells you what session 1 is currently executing. It does not (and can not) tell you that session 1 acquired the row-level lock that session 2 is waiting on 5 minutes ago by issuing a particular UPDATE statement.

    Justin
  • 6. Re: Oracle 11g :SELECT query blocked..??
    user12075620 Newbie
    Currently Being Moderated
    Thanks Justin for your comments.

    Here the confusion is why the SELECT statement is blocked by the UPDATE statement? Reads arenot blocked by writes and writes are not blocked by reads.
    on the top, it's a simple SELECT statement without FOR UPDATE and also it's not a distributed query?

    Can you shed some light on this, please?

    Regards,
    Boris
  • 7. Re: Oracle 11g :SELECT query blocked..??
    Justin Cave Oracle ACE
    Currently Being Moderated
    The SELECT statement is not blocking the UPDATE. As I said in the previous reply, the string that this query produces does not match the logic.

    This query is (at least on the surface) correctly identifying that session 1 is blocking session 2. Session 1 holds some lock that session 2 is waiting on. So far, so good. Since session 2 is waiting on the lock, we can easily enough see what session 2 is running (the UPDATE statement). But since session 1 is not blocked, it is potentially off running a ton of other SQL statements (or no SQL statement at all). The query is looking to see what session 1 is running currently. It has no way of determining what session 1 ran at some point in the past to acquire the lock in the first place.

    Going back to my KING example,

    At noon, session 1 runs
    UPDATE emp
       SET sal = sal * 2
     WHERE ename = 'KING'
    Session 1 now has a lock on the KING row in the EMP table. But session 1 neither commits nor rolls back, it is still in a transaction. Session 1 might not have any more activity for a long time-- the user might go off to lunch, for example (obviously, applications should not be designed to allow users to maintain open transactions indefinitely, but not all applications are designed correctly). Or it might start running other queries. Let's say that session 1 now runs a query that is going to go for an hour
    SELECT *
      FROM giant_view_with_lots_of_computations
    Now, at 12:45, session 2 comes in and runs
    UPDATE emp
       SET bonus = 100
     WHERE ename = 'KING'
    Session 2 is blocked. Session 2 is running the UPDATE statement. Session 1 still holds the lock but it is running some completely unrelated SQL statement.

    If we run the query you posted, the query will correctly report that session 1 is running the query against the GIANT_VIEW_WITH_LOTS_OF_COMPUTATIONS but incorrectly imply that this SELECT query is the source of the lock. It is not. It simply happens to be the query that the session that does hold the lock happens to be executing at the current moment (why the application seems to be running a lot of queries that select a constant from dual is a separate question).

    Justin
  • 8. Re: Oracle 11g :SELECT query blocked..??
    user12075620 Newbie
    Currently Being Moderated
    Thanks for your explanation. but my concern is "SELECT 1 FROM DUAL" is generated from Weblogic to test the db connectivity and this no way related to the blocked update.

    Am I missing something here?

    Exact query

    CT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=469 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY WHERE PURCHASE_ENTRY_ID=:1

    SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=367 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY WHERE PURCHASE_ENTRY_ID=:1



    Regards,
    Boris

    Edited by: user12075620 on Dec 4, 2012 11:39 AM
  • 9. Re: Oracle 11g :SELECT query blocked..??
    Justin Cave Oracle ACE
    Currently Being Moderated
    Correct. The SELECT is completely unrelated to the lock that is blocking the UPDATE.

    The point of my example is that once session 1 acquires the lock, it can go off and do anything (or nothing). It can query a view that will take an hour to return a response. It can issue a SELECT from DUAL. It can do nothing. Until it releases the lock by committing (or rolling back) its transaction, session 2 is still blocked. The query session 1 is running at the current instant is irrelevant to session 2. It is potentially relevant to you if it lets you figure out what session 1 is currently doing, why it might be holding the lock, and what you might want to do about it (i.e. whether you want to kill session 1 or whether you want to let it finish what it is doing).

    If you are saying that the SELECT 1 FROM dual query is a test of database connectivity, that would tend to imply that you have an application bug that is allowing a connection with an open transaction to be returned to a connection pool unless, for some reason, you are testing database connectivity in the middle of some other longer-running operation which would seem highly odd.

    Justin
  • 10. Re: Oracle 11g :SELECT query blocked..??
    user12075620 Newbie
    Currently Being Moderated
    Thanks for the pointers.


    I have gathering this information from asktom's site. The below quote is from one of the java experts


    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:45249405283766


    "We've weblogic server running and connection pool connects to Oracle database.

    In weblogic admin console there is a setting to check connection available every 60 seconds and
    it'll allow you to put a SQL statement to check the connection(select 1 from dual, you can put any
    SQL statement if you want to..)"


    I spoke with Java specialist now and he is blaming about oracle. They have checked the configuration and said immediately. We are just checking "SELECT 1 FROM DUAL" nothing else. why the query is locked??
    I was speechless? how to prove?

    FYI - His response was " weblogic parameters are well-tuned and there is no bug with that"

    Regards,
    boris

    Edited by: user12075620 on Dec 4, 2012 11:56 AM
  • 11. Re: Oracle 11g :SELECT query blocked..??
    Justin Cave Oracle ACE
    Currently Being Moderated
    The lock that session 1 holds is completely and totally independent of the query that session 1 is running. It would still hold the lock even if session 1 never ran another query. The SELECT 1 FROM DUAL query is completely unrelated to the lock that is held by session 1.

    If the only place that SELECT 1 FROM dual is being executed is from WebLogic issuing queries to test database connectivity, that implies that there is a bug in the Java application that is somehow allowing a connection with an open transaction to be returned to the connection pool. This has nothing to do with whether WebLogic is "tuned". It does seem likely that there is a way to force WebLogic to roll back any transaction that is open when a connection is returned to the pool (every reasonable connection pool implementation I've come across does this). But you'd need to look at the application to figure out how it is managing to leave the transaction open without either committing or rolling back. My guess would be that you've got some code that isn't handling exceptions correctly but that's just guessing.

    Justin
  • 12. Re: Oracle 11g :SELECT query blocked..??
    user12075620 Newbie
    Currently Being Moderated
    Thanks for your time Justin. Much appreciated. Let me check with application team and get back to you. Thanks so much for your time
  • 13. Re: Oracle 11g :SELECT query blocked..??
    yxes2013 Newbie
    Currently Being Moderated

    Hi Justin,

     

    Supposing the blocking session issues lots of queries, Is it possible to display all the sql statements owned by that session?

     

     

    Thanks

  • 14. Re: Oracle 11g :SELECT query blocked..??
    sb92075 Guru
    Currently Being Moderated

    yxes2013 wrote:

     

    Hi Justin,

     

    Supposing the blocking session issues lots of queries, Is it possible to display all the sql statements owned by that session?

     

     

    Thanks

    Stupid IDIOT!

    post SQL & results which shows which user owns which SQL statement.

    I bet you are incapable to do it.

     

    Guess why it is called the Shared Global Area?

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points