Sep 13, 2022 6:00:00 AM by Clarisa Maman Orfali
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
Where lock_mode refers to: EXCLUSIVE, SHARE, ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, ROW SHARE | SHARE UPDATE
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.
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
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.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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
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.