Unlock Table Bulletin For Free

Unlock An Oracle Table Get the object ID of the locked table: SELECT object_id FROM dba_objects WHERE object_name='YOUR TABLE NAME'; Get the SID values for this ID: SELECT sid FROM v$lock WHERE id1=OBJECT ID FROM STEP1. Get the session values for these SIDs: ... Kill the sessions causing the lock:

Table locks set within a transaction are released automatically when the transaction completes. If you are using an ANSI-compliant database, do not issue an UNLOCK TABLE statement.

Unlock An Oracle Table Get the object ID of the locked table: SELECT object_id FROM dba_objects WHERE object_name='YOUR TABLE NAME'; Get the SID values for this ID: SELECT sid FROM v$lock WHERE id1=OBJECT ID FROM STEP1. Get the session values for these SIDs: ... Kill the sessions causing the lock:

Answer: The only way to release a lock on an Oracle table is to kill the session that is holding the lock, or wait for the transaction to complete. See my related notes on finding Oracle locked_objects and killing_Oracle sessions.

When a deletion query using a WHERE clause starts, it will lock that table. All other queries that are run against the table will have to wait. Because of the massive size of the table, the deletion query would probably run for hours. The lock would be in place for hours, essentially halting the entire app.

InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES . You should not have autocommit = 1 , because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES , and deadlocks can very easily happen.

Type "Kill <session ID>" into the command prompt, and press "Enter." Replace "Session ID" with the session ID number you wrote down in Step 2. This kills the user's session and the SQL lock that was created.