CODEWITHSUNDEEP
oracle-databaseoracle11glocking
Leoh
Leoh

Reputation: 670

Lock a table in oracle

I want to Lock a table in oracle and then check if it is blocked, But i cant Lock a Table. I tried doing this to lock:

LOCK TABLE CLIENTS IN EXCLUSIVE MODE

this es the query to Lock , but i can still upadate in that table

then check if its Blocked:

SELECT COUNT(*) AS total
  FROM v$locked_object l
  JOIN dba_objects d
    ON l.object_id = d.object_id
 WHERE d.object_type = 'TABLE'
   AND d.object_name = 'CLIENTS'

How can I Lock a table and then check if its blocked with php.

Upvotes: 3

Views: 10008

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

you can use for update :

select * from clients for update

to lock the table's whole records in exclusive mode ( ROW_X (SX): Row Exclusive Table Lock --> "locked mode 3" ), but it's not recommended for production systems. At least lock for only restricted set of records filtering by where clause.

Your statement (LOCK TABLE CLIENTS IN EXCLUSIVE MODE) also locks whole table and called Exclusive (X): Exclusive Table Lock --> "locked mode 6" as the command with for update above, i.e. logic works for both SX and X locks wherenever the select statement is without a where clause. But in the first method, there's a chance to restrict the number of the records which are impacted by the locking operation.

commit or rollback should be issued to release the lock for both cases( SX or X type locks ).

Upvotes: 4

Related Questions