In this tutorial, we will understand the concept of Sessions and Transactions in Oracle database.
Session
Session – Each Session encapsulates user interaction from login to logout ( Or Till connection is lost due to Network connectivity issues )
We can start a session in Oracle using one of the following ways:
- Connection using Command Line Tools
- Connection using Third-Party GUI tool like SQL Developer
- Java/Python Program
Let’s have a look at following example where a user logs in to the Oracle Database using SQL developer,
Session 1:
7.00 pm – Login
SQL SELECT
UPDATE
DELETE
8.00 pm – Connection lost due to Network Error
Session 2:
8.30 pm – Connects again
SELECT UPDATE
9.00 pm – Logout
In this example, we saw that a user created two sessions to interact with the database.
A Single user can create multiple sessions at the same time using different methods of connection. So a user can be logged into the database using SQL developer and SQL command line tool at the same time.
Multiple users can also create multiple sessions and each session can run parallelly and interact with database at the same time.
Transaction
SQL commands are categorized into four categories:
DDL – Data Definition Language ( CREATE, ALTER, DROP, TRUNCATE, RENAME)
DQl – Data Query Language ( SELECT)
DML – Data Manipulation Language ( INSERT, UPDATE, DELETE )
DCL – Data Control Language ( GRANT, REVOKE)
Out of this, DML commands are used for making changes to the database and form the basis of a transaction.
Transaction
A transaction can be referred to as a unit of work performed on the database using DML statements. Each transaction groups a series of steps into a single execution unit which makes changes to the database. A transaction can only result in either: Success or Failure.
In Oracle database, the transaction starts when the first DML command is executed. A transaction that’s already in progress can end only with the occurrence of one of these events:
- When we end the query with either COMMIT or ROLLBACK statement
- Querying a DDL or DCL statement results in Auto-Commit.
- When a user exits SQL Developer or SQL Plus > Automatic Commit
- In case our system crashes or SQL Plus stops unexpectedly > Automatic Rollback
After one transaction ends, the next one starts when we issue a new DML statement
A good example of Transaction is when we use ATM for withdrawing money. A transaction in this case includes a set of steps where our withdrawal request of money needs to meet the condition of sufficient cash in the machine and in your bank account for successful withdrawal of money. Failing to meet these conditions or an error can result in failure of transaction.
A transaction by definition must be atomic, consistent, isolated and durable.
Commit
Changes that we make in a database using DML commands are not permanent until we COMMIT these changes. The COMMIT command saves all the changes that we made to the database since the last commit.
After COMMIT, these changes also reflect in other sessions as well.
ROLLBACK command helps to roll back to the last commit.
Rollback
The ROLLBACK command is used for undoing all the DML operations that we performed since the last commit. It ends the current transaction and restores the Oracle database to a previous state before we started the current transaction.
In an ATM withdrawal example, COMMIT is when the ATM machine asks us final time whether we want to proceed with the transaction and we press yes. The changes we made to database are permanent and money has been withdrawn which will reflect in your bank balance. ROLLBACK is when we are not able to complete a transaction because of an error and we need to start the transaction again.
Deadlock
Consider the following example where:
Process A ( Resource 1; Waiting for Resource 2)
Process B ( Resource 2; Waiting for Resource 1)
In this example, we can see that resource 1 is waiting for resource 2 and vice versa. This situation is known as a Deadlock as both processes are blocked because both resources are waiting for each other to complete.
Here, we run two sessions parallelly by logging into the database using both Command Line and SQL developer and we run these two queries simultaneously, to understand this clearly:
- Queries used in session 1
SELECT * FROM employees for UPDATE UPDATE departments SET location_id = location_id + 1
- -Queries used in session 2
SELECT * FROM departments for UPDATE UPDATE employees SET salary = salary + salary * 0.10
By running these two queries, we get ORA:00060 Error Deadlock detected while waiting for resource.
For resolving the deadlock, we need to terminate one process to free up the resources.
In this tutorial, we learned about Sessions and Transactions in Oracle Database.