SQL Developer: Executing queries in different transactions

The objective of this post is to explain how to execute queries in different transactions using SQL developer.

Introduction

The objective of this post is to explain how to execute queries in different transactions using SQL Developer. This way, we can execute queries simultaneously and test aspects related to concurrent transactions accessing the same tables and records.

Fortunately, this is very easy to achieve using SQL Developer, which offers us the possibility to use Unshared SQL Worksheets.

The code

We will start by creating a dummy table, for testing purposes. It will be a table consisting of workers, with a unique identifier per record (which will be the primary key), a worker name and a worker job.

create table Worker(
WorkerID varchar(20),
WorkerName varchar(255),
WorkerJob varchar(255),
CONSTRAINT PK_Worker PRIMARY KEY (WorkerID)
);

Note that since the creation of a table is a DDL statement, there is no need for commit because Oracle SQL auto-commits it.

After that, we will insert a record, as indicated bellow, and then commit the transaction. You can commit on the SQL Developer menu, above the worksheet, or with a manual statement. I’m assuming the commits are done in the menu, so I’m not putting them in the statements.

insert into worker values ('1','John', 'nurse');

After that, we should now have the new record on our database. In order to leave the record locked, we will now do an update on it, without committing the transaction.

UPDATE Worker
SET WorkerName = 'Smith'
WHERE WorkerID = 1;

Now, we will open a new Unshared SQL Worksheet, as indicated in figure 1. The SQL statements executed in this sheet will be in a different transaction (and also in a different database connection [2]).

Now, to test if this is working correctly, execute another update statement on the same record, on the new unshared worksheet.

UPDATE Worker
SET WorkerName = 'Jane'
WHERE WorkerID = 1;

Since we had a transaction that updated that same recorded and haven’t yet committed, the transaction on the new unshared worksheet will hang. To unlock it, simply go to the initial worksheet (the one where we created the table and performed the first update) and commit the transaction. The update in the unshared worksheet should no longer be hanging.

You can now commit the second transaction (in the unshared worksheet) and confirm that the record is now updated with a Worker Name equal to “Jane”, as shown in figure 2.

Figure 2 – Record after committing all the updates.

 

References

[1] https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_1001.htm#i2099120

[2] http://www.thatjeffsmith.com/archive/2013/01/a-sql-developer-primer-open-a-new-worksheet/