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]).

SQL Developer unshared worksheet.png

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.

SQL Developer Worker Table after commit

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/

Advertisements
This entry was posted in SQL and tagged , , , , , . Bookmark the permalink.

One Response to SQL Developer: Executing queries in different transactions

  1. Pingback: Oracle SQL: Row locking on select statement | techtutorialsx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s