Solved

How to abort the query?

Posted on 2000-02-23
3
990 Views
Last Modified: 2008-02-01
Hello Sir,

Presume I'm having two transactions. The first transaction locks a group of rows in a table. The second transaction wants to lock the same rows that is already locked by the first transaction. It is obvious that the second transaction has to be in the wait state.

I want to know the details about the following questions.

1) Is there any possibility to check out the status of a row/ group of rows whether its locked/unlocked? ( The first transaction locked a record. The second transaction should know the status of the record before raising the query).

2) Is there any way to abort the query that is raised by a particular transaction? ( In the above case the second transaction)

3) Is there any option available to specify the time slice for a particular query? ( If the query is not executed with in the stipulated period it has to be wiped out from the queue. In the above case the query raised by the second transaction should not wait for more than a time period and the second transaction should resume its process)


Regards,

T.EMMANUEL BASKAR
0
Comment
Question by:tebsk
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
mshaikh earned 100 total points
Comment Utility
1..    Yes.  When a session updates a row in a table the row is locked
         by the sessions  transaction. Other users may SELECT that
         row and will see row as it was  BEFORE the UPDATE occurred.
         If another session wishes to UPDATE the same row it has to wait
         for the first session to commit or rollback. The  second session
         waits for the first sessions TX lock in EXCLUSIVE mode.

         Eg:
         Say tx_eg is a table which will be updated by to session Ses#1
         and Ses#2.  
 
         Ses#1:  update tx_eg set txt='Garbage' where num=1;
         Ses#2:  update tx_eg set txt='Garbage' where num=1;
         DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                  from v$lock where type='TX';

         SID        TY ID1        ID2        LMODE      REQUEST
         ---------- -- ---------- ---------- ---------- ----------
                  8 TX     131075        597          6          0
                 10 TX     131075        597          0          6

         > This shows SID 10 is waiting for the TX lock held by SID 8 and it
         > wants the lock in exclusive mode (as REQUEST=6).

         The select below is included to demonstrate that a session waiting
         on a lock will show as waiting on an 'enqueue' in V$SESSION_WAIT
         and that the values of P1RAW, P2 and P3 indicate the actual lock
         being waited for. When using Parallel Server the EVENT will be
         'DFS enqueue lock acquisition' rather than 'enqueue'.
         This select will be omitted from the following examples.

         DBA:    select sid,p1raw, p2, p3
                   from v$session_wait
                  where wait_time=0 and event='enqueue';

         SID        P1RAW    P2         P3
         ---------- -------- ---------- ----------
                 10 54580006     131075        597
         >          ~~~~  ~~     ~~~~~~        ~~~
         >          type|mode       id1        id2
         >           T X   6     131075        597


         The next select shows the object_id and the exact row that the
         session is waiting for. This information is only valid in V$SESSION
         when a session is waiting due to a row level lock. The statement
         is only valid in Oracle 7.3 onwards. As SID 10 is the waiter above
         then this is the session to look at in V$SESSION:

         DBA:    select ROW_WAIT_OBJ#,
                      ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
                       from v$session
                       where sid=10;

         ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
         ---------- ---------- ---------- ----------
               3058          4       2683          0

         > The waiter is waiting for the TX lock in order to lock row 0
         > in file 4, block 2683 of object 3058.

         Ses#1:  rollback;
         Ses#2:  rollback;

2..    Yes there is.
        a)  You can CTRL-C out of the query if you issued the query
              from an SQLPLUS prompt
        b) You can kill the session that is waiting or the session that is
             locking using 'ALTER SESSION KILL 'sid,serial#'

3..   There is no option available to specify the time a query is going to wait
        for alock
           
0
 

Author Comment

by:tebsk
Comment Utility
Hi Mshaikh,

  I'm basically on C++ and I don't know anything about ORACLE.

 I just want to know that,

 any method availabe for finding out status of the record in the table ( What I'm asking is before issuing any DML query)? If so, please explain me in detail.

Regards,

Baskar
0
 
LVL 6

Expert Comment

by:mshaikh
Comment Utility
You can't do that before the query because you will have to know the block number, file number in which your row to be updated is located. By the time you figure this information out  the lock may have been released. Best way is to issue you query and if you get a locking problem then deal with it using the method described above.

You can also minimize locking problems if in your code for different modules you make sure that you update tables in acertain order. That is if Procedure A updates table_1 and then table_2 then in procedure B  also update in the same order. Reversing the order increases the probability of locking problems.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now