Toad World Blog

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

Sep 13, 2022 6:00:00 AM by Clarisa Maman Orfali

oracle lock table 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


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



  • 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

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

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

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

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

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

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.


  • 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.


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:


Figure 7: Block Table declaration in exclusive mode

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

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

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.


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.


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

Figure 10: Session Browser – Locks Tab


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.


Figure 11: Session Browser – Locks Tab

Figure 11: Session Browser – Locks Tab


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.


Tags: Toad for Oracle

Clarisa Maman Orfali

Written by Clarisa Maman Orfali

Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area.

Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses.

She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707).

Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.