Reputation: 670
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
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
orrollback
should be issued to release the lock for both cases(SX
orX
type locks ).
Upvotes: 4