What is the Oracle lock table function, and how does it work?

This article will talk about an important function that we need to understand when working with an Oracle database, the Oracle LOCK TABLE. First of all, Oracle blocks information based on the transactions that happen within our system. So it is possible to make changes within our database, and before we commit those operations, the transactions will be blocked, preventing any other user from updating or making any modification in the rows affected by the transaction in which we are working. When we use the Oracle LOCK TABLE statement in the Oracle database to lock one or more tables, table partitions, or table subpartitions in a specific way, we manually override Oracle’s automatic locking feature. In this way, we can allow or deny the lock access to a table or view by other users during the duration of our operation.

Some types of locks can be used on the same table simultaneously. However, other types of locks only allow one lock per table.

To use this statement, the table or view must:

– be in our scheme, or

– have the LOCK ANY TABLE system privilege, or

– have any object privilege (except READ object privilege) on the table or view

Syntax

The following is the syntax for the LOCK TABLE statement:

LOCK TABLE [schema.]table_name [options] IN lock_mode MODE [WAIT | NOWAIT];

Where the options are: PARTITION, SUBPARTITION, @dblink

Where lock_mode refers to: EXCLUSIVE, SHARE, ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, ROW SHARE | SHARE UPDATE

Parameters:

• Table_name: This parameter is the name of the table. If we specify a View, Oracle locks the base tables of the view.
• Lock_mode: Refers to the mode in which we will lock the table. There are different modes:
  • ROW_SHARE: This mode allows simultaneous access to the locked table but prohibits users from locking the entire table for exclusive access.
  • ROW_EXCLUSIVE: This mode is the same as ROW SHARE, but it also prohibits locking in SHARE mode. The difference between this mode and ROW SHARE is that it doesn’t allow blocking in shared mode.
  • SHARE_UPDATE: It is similar to ROW_SHARE. It allows concurrent access to the table. That is, multiple users can access the table. It doesn’t allow users to lock the entire table.
  • SHARE: This mode permits concurrent queries but prohibits updates to the locked table.
  • SHARE ROW EXCLUSIVE: is used to look at a whole table and allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows.
  • EXCLUSIVE: This mode allows only SELECT queries on the locked table. Other types of activities are not allowed.
• WAIT – This keyword, when used, allows the database to wait until the table is available and then lock the table and return control to the user.
• NOWAIT – This keyword refers to the condition where the database doesn’t wait to release the lock. This keyword is useful when the user wants the database to return control to them immediately.

Before knowing how the Oracle Lock Table instruction works, let's see how Oracle's automatic locking works.

We start a session in Toad for Oracle for this demo, and we will use the HR schema. In this schema, we have a table called Employees.

Figure 1: Schema HR – Employees Table

To understand auto-lock, we are going to perform an update to this table with this simple statement:

update hr.employees set salary = 10000;

For demonstration purposes only, this statement will update all employees' salaries to $10,000.

Note: Toad advises against using an update or delete statement without a where clause.

Figure 2: Update Salary

If we now see the table of employees, all employees have a salary of 10,000.

Figure 3: Salary column updated

This transaction is not committed yet, so Oracle has locked all these records until the operation is confirmed. So, Oracle is locking all these records because someone is working on those records. And Oracle will unlock these records when the process is confirmed, or the rollback is performed. That way, the records will be released.

Let's see what happens when another user wants to update the same records. Again, we open a new session, but in this case, we will use SQLPlus.

Looking at the contents of the Employees table, we can see that the salaries of the employees have not changed yet; salaries are displayed before the update made in the session in Toad:

select employee_id, first_name, last_name, salary from employees;

Figure 4: Salary Column

In the Toad session, we see all employee salaries change to 10,000, but in the SQLPlus session, we don't see it because the commit or rollback hasn't been done yet.

We will try to update an employee's salary in this SQLPlus session for this demonstration.

update hr.employees set salary = 5000 where employee_id = 100;

Figure 5: SQLPlus Session

We see that SQLPlus is waiting and can't do anything because another session locks that record. So that is why we don't see anything happening in SQLPlus.

If we rollback the transaction on the Toad session, the records are freed, and then we'll immediately see the SQLPlus session perform the update operation.

Figure 6: Update record on SQLPlus

Now that we know how Oracle's automatic lock works let's see how the Oracle Lock Table statement works.

How does the Oracle LOCK TABLE statement work?

When we execute the Oracle LOCK Table statement, the database overrides the manual automatic locking available in Oracle and allows or denies other users to view or update the table for a specified time, depending on what we indicate in the statement.

If we assume that the user has chosen EXCLUSIVE mode, then other users can only run SQL queries on that table, and the database will not allow any updates on that table by other users. On the other hand, if it is ROW SHARE mode, the database will not allow the user to lock the entire table and allow simultaneous access to the table.

So it depends on the mode type that we tell the database, and depending on that, the database will lock our table or tables.

Considerations:

• A normal SELECT statement doesn't lock rows.
• The INSERT, UPDATE or DELETE statements perform an EXCLUSIVE ROW lock on the rows affected by the WHERE clause.
• The SELECT … FOR UPDATE NOWAIT statement perform a ROW EXCLUSIVE lock on the rows affected by the WHERE clause.
• COMMIT and ROLLBACK statements unlock previously locked rows within the current transaction.
• Even if a row is locked (by another transaction), we can always do a SELECT query on that row. The values returned are those before the lock.

Let's see some examples of the use of LOCK TABLE.

Example 1: EXCLUSIVE MODE NOWAIT

We continue working with the HR schema and the employee's table.

We identify the SID of our session:

select sys_context('USERENV','SID') SID from dual;

In my case, it is 280.

The following statement locks the employees table exclusively and doesn't wait if another user previously locked the table:

LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;

Figure 7: Block Table declaration in exclusive mode

Now from SQLPlus, we identify our SID. In my case, the SID is 276, and we proceed to perform an update on the table.

Let's increase the salary of the employee, Steve, whose employee number is 100.

update hr.employees set salary = 5000 where employee_id = 100;

Figure 8: Update locked table in another session in SQLPlus

As we can see in Figure 8 when we finish executing the update, our session will remain to wait, and this is because we already have the table locked using an exclusive lock in the Toad session.

Suppose a user calls us and tells us that their session is not responding. In this case, we must first identify if there is a lock in the database. From Toad for Oracle, we enter the Session Browser screen as users with DBA privileges. Then, from the main navigation menu, we click on Database à Monitor à Session Browser. The following screen is shown to see the lock of the EMPLOYEES table.

Figure 9: Session Browser – Locks Tab / Blocking locks

We can see that the ID of the lock is 280. That is, the first session opened in Toad.

Example 2: SHARE MODE NOWAIT

The following statement locks the employee's table in share mode without waiting. It is important to note that we can't get exclusive locks once a shared lock is on a table. And similarly to the previous example, the NOWAIT means that it will not wait for a lock to be released.

LOCK TABLE hr.employees IN SHARE MODE NOWAIT;

In this demo, we have two open sessions (SID 283 and 290) in sharing mode, and in session 290, we can't perform DML operations until session 283 releases the table lock.

We can see the lock type in the Session Browser window in a new Toad session.

Figure 10: Session Browser – Locks Tab

Example 3: EXCLUSIVE MODE WAIT

In this example, we will use the Exclusive mode of operation. We will lock the employee table in Exclusive mode with WAIT, which means the database will wait until the employee table is available and then lock it.

LOCK TABLE hr.employees IN EXCLUSIVE MODE WAIT 5;

Figure 11: Session Browser – Locks Tab

Conclusion

This article showed how Oracle handles automatic locking, the definition of the Oracle LOCK TABLE statement, and its syntax. Additionally, we review the various LOCK modes available and how they work.

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.