An Introduction to

Transaction Management, Concurrent Access, Lockwaits, and Deadlocks

For Developers

Most databases are transaction processing databases if they are not data warehouses or data marts. Transaction management is probably the most important concept that a developer needs to know if they are going to work with a database.

Definition of Transaction Management

It is the responsibility of the business analysts and developers to define the transactions and make sure that they are implemented properly. There are two definitions of transaction management depending on your point of view:

· Developer definition: A transactions is a set of database changes (inserts, updates, and deletes) such that you want all of them saved or none of them saved.

· DBA definition: All the DML statements (inserts, updates, and deletes) between two COMMITs, or a COMMIT and a ROLLBACK.

The typical example of a transaction is a program that deposits a check in a bank account. This actually involves two steps:

1. The first account is debited for the amount on the check.

2. The second account is credited for the amount on the check.

You want both of the changes saved or neither of them saved. If the first step fails, for “insufficient funds” or any other reason, then you don’t want to credit the second account. If the second step fails, for “account closed” or any other reason, then you don’t want to debit the first account. Both steps make up a single transactions.

Without proper transaction management, the first step could be saved even if the second step fails, or vice versa. The result is called “inconsistent data.” With proper transaction management, the data is in a consistent state.

Another example of a transaction is the purchase of a widget from an online website. This transaction is made up of at least two steps: debiting your credit card, and sending you the widget. If the charge to your credit card bounces, then they won’t want to send you the widget. If they can’t send you the widget, because they are out of stock or something, then they shouldn’t charge your credit card. Both steps must succeed or the transaction is cancelled.

Implementation of Transaction Management in PL/SQL

All programming languages can implement transaction management. This is typically done using COMMIT and ROLLBACK statements. You may also see the ANSI compliant COMMIT WORK and ROLLBACK WORK versions of these statements. The following is an outline of a PL/SQL block that implements a transaction:

COMMIT; -- this is the previous commit

BEGIN

INSERT …

UPDATE …

DELETE …

COMMIT;

EXCEPTION WHEN OTHERS THEN

ROLLBACK;

END;

If no error occurs during the execution of the SQL statements, then the COMMIT statement will run and execution will continue after the END of the block. If any of the SQL statements throws and exception (causes an error) then execution will jump to the exception handling section and the ROLLBACK statement will run. A rollback statement will undo all the changes made since the previous commit. Good PL/SQL coding will contain a lot more than this, but this example illustrates how exception handling is done. All coding languages that connect to databases can issue COMMIT and ROLLBACK statements, and handle exceptions.

This is the most common way to implement transactions, but not the only one.

A transaction can have any number of DML statements in it. It can have thousands, or it can have 1. The statements in the transaction will be defined by the business needs of the application.

Manually Creating Transactions in SQLPlus.

Here are some examples that create transactions in SQLPlus manually. First let’s create a table and insert a row into it:

SQL1>CREATE TABLE trans_test(

2 col1 INTEGER,

3 col2 VARCHAR2(2000),

4 constraint trans_test_pk PRIMARY KEY ( col1 )

5 );

Table created.

SQL1>INSERT INTO trans_test ( col1, col2 ) VALUES ( 1, 'Initial value for row1' );

1 row created.

If we query the table, we can see the row, even though it hasn’t been commited yet.

SQL1>SELECT * FROM trans_test;

COL1 COL2

--------- ------------------------------------------------------------

1 Initial value for row1

If we commit, then we still see the row.

SQL1>COMMIT;

Commit complete.

SQL1>SELECT * FROM trans_test;

COL1 COL2

---------- -----------------------------------------------------------

1 Initial value for row1

Now, let’s do a rollback test. We insert a second row.

SQL1>INSERT INTO trans_test ( col1, col2 ) VALUES ( 2, 'We are going to roll this back.' );

1 row created.

SQL1>SELECT * FROM trans_test;

COL1 COL2

---------- -----------------------------------------------------------

1 Initial value for row1

2 We are going to roll this back.

Notice that we can see the row once again. Now, let’s do a rollback and see what happens.

SQL1>ROLLBACK;

Rollback complete.

SQL1>SELECT * FROM trans_test;

COL1 COL2

---------- -----------------------------------------------------------

1 Initial value for row1

After we issue the ROLLBACK statement, the second row is gone.

Logons and Logoffs.

When your session connects to a database, your first transaction starts. It will continue until your first commit or rollback. When you disconnect your session normally from the database, most applications will issue an automatic commit. “Commit on exit” is typically default behavior, but most application are configurable, so this behavior can be disabled. If your session is disconnected abnormally from the database, no commit is issued and all your work since the last commit will be lost. This will happen if you disconnect your network cable from your PC, or remove your laptop from the docking station.

Database Crash

If the database crashes, because the power failed, or some other reason, then all uncommitted transactions will be rolled back when the database starts up. This will put the data in a “consistent state.”

Other Sessions

SQL statements in other sessions, with separate database connections, have separate transactions even if they are from the same application.

SQLPlus Exit Options

SQLPlus has commands that can configure exit behavior. For example:

WHENEVER SQLERROR EXIT 3 ROLLBACK

This command will issue a rollback statement and exit if any SQL statement in the script causes an error. In unix, it will also return an exit code of 3 to the shell script or session that ran the SQLPlus script.

Autocommit

Some applications can be configured to autcommit. This means that the application will commit automatically after every SQL statement you issue. You do not need to execute a COMMIT statement yourself. In SQLPlus, this behavior is disabled by default, but can be enabled with the following SQLPlus command:

set autocommit on

and disabled with

set autocommit off

JDBC, on the other hand, has autocommit enabled by default. In order to do transaction management, you must disable autocommit and control the transactions programmatically with your own COMMIT and ROLLBACK statements.

SQL Statements in Transactions

There are three types of SQL statements:

· DML, Data manipulation language

o INSERT

o UPDATE

o DELETE

o SELECT

· DDL, Data Definition Language

o CREATE (table, index, view or anything else)

o ALTER (table, index, view,or anything else)

o DROP …

· DCL, Data Control Language

o GRANT

o REVOKE

Transactions are made up of DML statements, with the exception of SELECT, which does not make any changes to the database. All DDL and DCL statements have implicit commits before and after them. They will commit your work and they cannot be rolled back. Remember that when coding your transactions.

Calling Procedures and Firing Triggers

A transaction exists within a database session, also thought of as a connection to the database. All the SQL statements in that session between commits and rollbacks are part of a transaction. If you call a procedure or function, it is part of your transaction. Any COMMIT or ROLLBACK statements in those program units will end your current transaction and start a new one. If you fire a trigger, it is part of your transaction. If you then execute a ROLLBACK, any changes made by the trigger will be undone.

Some database procedures have commits in them, such as DBMS_STATS.GATHER_TABLE_STATS(). You should test your procedure calls to determine if they are going to affect your transactions.

There is an exception for procedures that are created with definer rights. These procedures will contain the line “PRAGMA AUTONOMOUS_TRANSACTION“ in the declaration section. The changes made in such procedures are independent, and any COMMIT or ROLLBACK statements in such procedures will have no effect on the on the calling transaction.

Transactions are typically implemented with database procedures or SQL scripts. They are less common in functions. Functions that are used in SELECT statements cannot have transactions in them. You can’t put them in triggers either since a trigger is part of the transaction of the SQL statement that fires it.

Concurrent Access in Oracle: update by one session and read by another

Concurrent access is related to transaction management in Oracle. Remember that each session has its own transactions. Let’s see how those transactions interact. We will start with an update by one session and a read by another. First I will insert into the table with one session, and then try to see the new row with a second session.

SQL>set sqlprompt SQL1>

SQL1>INSERT INTO trans_test ( col1, col2 ) VALUES ( 2, 'Can you see me?' );

1 row created.

SQL1>SELECT * FROM trans_test;

COL1 COL2

---------- ----------------------------------------

1 Initial value for row1

2 Can you see me?

Notice that I have not commited the insert yet. Now I’m going to log into the database with a second SQLPlus session and select from the table.

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 27 22:18:22 2011

SQL> set sqlprompt SQL2>

SQL2>

SQL2>column col2 format A60

SQL2>SELECT * FROM trans_test;

COL1 COL2

---------- ------------------------------------------

1 Initial value for row1

The second session cannot see the new row yet. Now, I will go back to the first session and commit the change.

SQL1>COMMIT;

Commit complete.

SQL2>SELECT * FROM trans_test;

COL1 COL2

---------- ---------------------------------

1 Initial value for row1

2 Can you see me?

Now the second session can see the new row.

So the moral of the story is that in Oracle, no other session can see your DML changes until you commit your work. Other sessions always see the last commited version of the data. Their view of the data is always transaction consistent. There are no dirty reads in Oracle like there are in certain other RDBMS systems.

If you edit data in a database using a query tool like Toad, SQL Developer , Golden, or SQLPlus, just remember that no other session can see your changes until you click the commit button.

Concurrent Access in Oracle: lock waits, update of the same row by two different sessions.

First, we update row 1 of the table from the first session again. Then we will update the same row from session 2.

SQL1>UPDATE trans_test

2 SET col2 = 'Update from session 1'

3 WHERE col1 = 1;

1 row updated.

SQL1>COMMIT;

Commit complete.

SQL2>UPDATE trans_test

2 SET col2 = 'Update from session 2'

3 WHERE col1 = 1;

1 row updated.

SQL2>commit;

Commit complete.

Notice how that works. We commited after each update. Now we will update from session 1 again, but this time we will not commit. When we try to update from session 2 again, nothing happens.

SQL1>UPDATE trans_test

2 SET col2 = 'Update from session 1'

3 WHERE col1 = 1;

1 row updated.

SQL2>UPDATE trans_test

2 SET col2 = 'Update from session 2'

3 WHERE col1 = 1;

Session 2 hangs. No error message is given. Session 1 has a lock on the row so that no other session can edit the same row until session 1 signals that it is done with it. This happens when session 1 issues a commit. Only then will session 2 stop hanging and complete its update.

SQL1>COMMIT;

Commit complete.

1 row updated.

SQL2>

Now session 2 has row 1 locked. Session 1 will have to wait if it tries to update row 1, but notice that it can still update row 2.

SQL1>UPDATE trans_test

2 SET col2 = 'Yes, I can see you'

3 WHERE col1 = 2;

1 row updated.

SQL1>COMMIT;

Commit complete.

Oracle DML locks are row level locks. There is also a DDL lock on the table, but all it does is prevent any ALTER TABLE statements from changing the table definition while an uncommitted transaction still has row locks. That is important to remember if you are running DDL scripts.

Viewing Locks in Oracle 11

If you have a session that hangs, the easiest way to find out if you are waiting for a DML lock held by another user, is to use the V$session view. Lets create another lock wait.

SQL1>UPDATE trans_test

2 SET col2 = 'Update from session 1'

3 WHERE col1 = 1;

Session 1 hangs because session 2 has not commited its update of row 1 yet. Now we query the v$session view to see the lock.

SQL2>column username format A15

SQL2>column osuser format A15

SQL2>SELECT sid, username, osuser, blocking_session

2 FROM v$session

3 WHERE blocking_session is not null;

SID USERNAME OSUSER BLOCKING_SESSION

---------- --------------- --------------- ----------------

197 DW_BAT tysonke 167

The blocking_session column indicates a lock wait. You can then query the v$session view again to find out more information about who is blocking you, such as application name, module, sql_id, machine, and other identifying information. An abbreviated list of columns from the V$session view is used for the purpose of this example so that the output can fit on the page.

SQL2>SELECT sid, username, osuser, program

2 FROM v$session

3 WHERE sid = 167;

SID USERNAME OSUSER PROGRAM

---------- --------------- --------------- ------------------

167 DW_BAT tysonke sqlplusw.exe

As you can see, it is my other session that has the lock. We can release the lock by entering a commit.

SQL2>COMMIT;

Commit complete.

Lock waits can also occur with insert and delete statements as well as updates.

Deadlocks

A dead lock occurs when two sessions are waiting for each other. To see how this happens, we will update row 1 with session 1 without committing the change. Then we will update row 2 from session 2 without a commit as well.

SQL1>UPDATE trans_test

2 SET col2 = 'Update from session 1'

3 WHERE col1 = 1;

1 row updated.

SQL2>UPDATE trans_test

2 SET col2 = 'Update from session 2'

3 WHERE col1 = 2;

1 row updated.

Then we will attempt to update row 2 from session 1, but we will get a lock wait. When we update row 1 from session 2 we also get a lock wait.

SQL1>UPDATE trans_test

2 SET col2 = 'Update from session 1'

3 WHERE col1 = 2;

SQL2>UPDATE trans_test

2 SET col2 = 'Update from session 2'

3 WHERE col1 = 1;

Now both sessions are waiting for each other. This is called a deadlock. Oracle automatically detects deadlocks within three seconds and arbitrarily errors out one of the waiting SQL statements.

UPDATE trans_test

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

The second update in session 1 has returned an error, but the first update still has row 1 locked. Session 2 is still waiting to update row 1. If we issue a commit in session 1, the update for session 2 will complete now.

SQL1>commit;

Commit complete

SQL2>

1 row updated.

Deadlock errors will usually send an alert notification to the DBAs, depending on how they have configured their alerts. Your application should notify you of the deadlock error just as it should notify you about any error, but the DBAs may forward the deadlock error message to you as well. The DBA message may include information about both sessions involved in the deadlock and what SQL they were running.

Deadlocks are application problems, not database problems. The developers need to design their applications so that deadlocks do not occur, or so that the risk of deadlocks is minimized.

Causes of lock waits and deadlocks

When there are major lock wait or deadlock problems in Oracle, it is frequently because of application design issues. The application sessions may have no commit statements at all; they may rely on the “commit on exit” application behavior. Without commits, many rows in different tables can be locked until the application session exits.

Another source of lock waits and deadlocks is the “SELECT FOR UPDATE” SQL statement. This type of select locks all rows in the query. If there are no filter predicates in the query, then all rows in the table will be locked. These rows will remain locked until a commit or rollback is issued. If this SQL statement is issued by a query tool by someone who then goes out for lunch, other users may wonder why their sessions are hanging.

Data Warehouses and Datamarts

Data Warehouses and Datamarts are typically not considered transaction processing databases. However, from the database point of view, all DML changes to them are still made using transactions. These transactions may not be defined by business needs, but they will still be composed of all the DML statements between commits. All the considerations involving concurrent access, lock waits, and deadlocks still apply.

When loading data into data warehouses, the main consideration is not transaction management, but a similar form of error handling. The question that must be addressed is “what happens if any step in the data load causes an error?” Can you simple restart the data load? If so, will it pick up from where it left off, or start over from the beginning? Will data cleanup be needed before the restart? (This is best avoided.)

Your data load may be one big transaction, where the entire load is saved if it completes successfully, or none if it is saved if there is an error. On the other hand, your data load may be divided up into many small transactions. It may even commit one row at a time. Such implementation decisions are typically governed by the need for effective error handling, and may also be governed by performance needs.

The point is that data loads are ultimately made by DML statements between commits. The database will consider these to be transactions even if you don’t.

There may still be a need for consistent data at the end of the data load, but it may be different from the simple transaction management model.

Additional information

This introduction only covers some of the subjects related to transaction management. Other subjects include:

· Transaction savepoints to be used with ROLLBACK TO SAVEPOINT.

· Repeatable reads with read-only transactions.

· Serializable transactions

· Transaction management of distributed queries over database links(two phase commit.)

· Much more

This presentation has been tailored for developers. For DBAs, there are other aspects of transaction management which includes rollback segments, redo logs, system change numbers (SCN), consistent database recovery, and other subjects.

For more information, see:

· Oracle 11 Concepts manual

o Chapter 10, Transactions

o Chapter 9, Data Concurrency and Consistency

· Oracle 11 Advanced Application Developer’s Guide

o Chapter 1, SQL Processing for Application Developers

· Oracle 11 SQL Reference Manual entries for

o COMMIT

o ROLLBACK

o SAVEPOINT

o SET TRANSACTION

o LOCK TABLE

· You can also Google “Database Transaction Management” where you will find the Wikipedia entry among other sites.