lock/unlock tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: lock/unlock tables

Hybrid View

  1. #1
    Join Date
    Aug 2002
    Posts
    27
    Hi all,
    If in SQL 7.3 I lock a table using the folwoing command:

    lock table tmp_problem_file in share mode nowait;

    Then how to unlock?????

    Thanks and Regards

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    By executing either COMMIT or ROLLBACK.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Posts
    27
    Hi,
    Thank you very much...
    However, suppose that I do COMMIT then I decide to unlock the table what should I do?
    Regards

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    may be u can try

    alter system kill session 'sid, serial#';

    following query shows which table has been locked in what mode , u will get sid and serial # also from this .
    ----------------------------------

    select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID||','||S.SERIAL# Kill, U1.NAME||'.'||substr(T1.NAME,1,20) tab,
    decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode,
    decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive',4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 whereL.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5

    [Edited by prakashs43 on 10-02-2002 at 08:23 AM]
    siva prakash
    DBA

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by omran
    However, suppose that I do COMMIT then I decide to unlock the table what should I do?
    Once you do COMMIT all your locks are released, so the table is no longer locked. So you can't "decide to unlock the table", it is already unlocked.

    You can never explicitely unlock the tables you have locked - it can be done only implicitly by COMMIT or ROLLBACK. There is no third way.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Posts
    27

    Thumbs up

    THANK YOU ALL FOR YOUR VALUABLE HELP!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width