How to Lock a table or record in the Oracle using query

Posted on 2005-04-15
Last Modified: 2012-06-21
Hi friends
 I want to lock the table using the query in oracle so can any body tell how I will do the same  I know  how to lock the table in SQLServer  as

begin transaction
update <table> set <field>=<value> where <condition>
This will lock the table. You can open another query analyzer window/session in sqlserver, and run a select * from <table>, it will block.

the same thing i want to do in oracle so can anybody tell me how I can do this in oracle

Question by:vvs
    LVL 22

    Accepted Solution

    The Oracle locking mechanism is different from SQL Server one. You can read this documentation - the registration is free:
    LVL 8

    Expert Comment

    by:Pierrick LOUBIER
    You can use SELECT FOR UPDATE
    LVL 11

    Expert Comment

    like "select * from emp where deptno = 20 for update; " will lock all the rows with DEPTNO=20. This lock can be released by either issuing a COMMIT or ROLLBACK. You can also use "for update of ename" or "for update NOWAIT". Issue the lock before using UPDATE/DELETE statement.
    LVL 25

    Expert Comment

    I don't believe there is any way to get Oracle to do that.  The approrpiate way to lock would be

    lock table {table_name} in exclusive mode;

    However, this will only prevent changes...a query against the table is still possible.  Oracle ensures consistent reads, and doesn't prevent locks from giving you a consistent view to the data.

    Author Comment

    ya I am accepting the answer here for Oracle can any one tell me the sme for Db2
    I mean how I can lock the table in db2

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Read about achieving the basic levels of HRIS security in the workplace.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now