Chapter 3

Overview

One of the key challenges in developing multi-user, database-driven applications is to maximize concurrent access but, at the same time, to ensure that each user is able to read and modify the data in a consistent fashion. The locking and concurrency controls that allow this to happen are key features of any database, and Oracle excels in providing them. However, Oracle's implementation of these features is unique and it is up to you, the application developer, to ensure that when your application performs data manipulation, it uses these mechanisms correctly. If you fail to do so, your application will behave in an unexpected way and, inevitably, the integrity of your data will be compromised (as was demonstrated in Chapter 1 on Developing successful Oracle Applications).

In this chapter we're going to take a detailed look at how Oracle locks data, and the implications of this model for writing multi-user applications. We will investigate the granularity to which Oracle locks data, how Oracle achieves 'multi-version read consistency', and what that all means to you, the developer. When appropriate, I'll contrast Oracle's locking scheme with other popular implementations, mostly to dispel the myth that 'row level locking adds overhead'. It only adds overhead if the implementation adds overhead.

What are Locks?

A lock is a mechanism used to regulate concurrent access to a shared resource. Note how I used the term 'shared resource', not 'database row'. It is true that Oracle locks table data at the row level, but it also uses locks at many other levels to provide concurrent access to various resources. For example, while a stored procedure is executing, the procedure itself is locked in a mode that allows others to execute it, but will not permit another user to alter it in any way. Locks are used in the database to permit concurrent access to these shared resources, while at the same time providing data integrity and consistency.

In a single-user database, locks are not necessary. There is, by definition, only one user modifying the information. However, when multiple users are accessing and modifying data or data structures, it is crucial to have a mechanism in place to prevent concurrent modifications to the same piece of information. This is what locking is all about.

It is very important to understand that there are as many ways to implement locking in a database as there are databases. Just because you are experienced with the locking model of one particular RDBMS does not mean you know everything about locking. For example, before I got heavily involved with Oracle, I used other databases such as Sybase and Informix. All three of these databases provide locking mechanisms for concurrency control, but there are deep and fundamental differences in the way locking is implemented in each one. In order to demonstrate this, I'll outline my progression from a Sybase developer to an Informix user and finally an Oracle developer. This happened many years ago, and the Sybase fans out there will tell me 'but we have row-level locking now'. It is true: Sybase now uses row-level locking, but the way in which it is implemented is totally different to the way in which it is done in Oracle. It is a comparison between apples and oranges, and that is the key point.

As a Sybase programmer, I would hardly ever consider the possibility of multiple users inserting data into a table concurrently - it was something that just didn't often happen in that database. At that time, Sybase provided only for page-level locking and, since all the data tended to be inserted into the last page of non-clustered tables, concurrent inserts by two users was simply not going to happen. Exactly the same issue affected concurrent updates (since an UPDATE is really a DELETE followed by an INSERT). Perhaps this is why Sybase, by default, commits or rolls back immediately after execution of each and every statement.

Compounding the fact that, in most cases, multiple users could not simultaneously modify the same table, was the fact that while a table modification was in progress, many queries were also effectively blocked against that table. If I tried to query a table and needed a page that was locked by an update, I waited (and waited and waited). The locking mechanism was so poor that providing support for transactions that took more than a microsecond was deadly - the entire database would appear to 'freeze' if you did. I learned a lot of bad habits here. I learned that transactions were 'bad', that you ought to commit rapidly and never hold locks on data. Concurrency came at the expense of consistency. You either wanted to get it right or get it fast. I came to believe that you couldn't have both.

When I moved on to Informix, things were better, but not by much. As long as I remembered to create a table with row-level locking enabled, then I could actually have two people simultaneously insert data into that table. Unfortunately, this concurrency came at a high price. Row-level locks in the Informix implementation were expensive, both in terms of time and memory. It took time to acquire and 'unacquire' or release them, and each lock consumed real memory. Also, the total number of locks available to the system had to be computed prior to starting the database. If you exceeded that number then you were just out of luck. Consequently, most tables were created with page-level locking anyway, and, as with Sybase, both row and page-level locks would stop a query in its tracks. As a result of all this, I found that once again I would want to commit as fast as I could. The bad habits I picked up using Sybase were simply re-enforced and, furthermore, I learned to treat a lock as a very scarce resource, something to be coveted. I learned that you should manually escalate locks from row-level to table-level to try to avoid acquiring too many of them and bringing the system down.

When I started using Oracle I didn't really bother reading the manuals to find out how locking worked here. After all, I had been using databases for quite a while and was considered something of an expert in this field (in addition to Sybase and Informix, I had used Ingress, DB2, Gupta SQLBase, and a variety of other databases). I had fallen into the trap of believing that I knew how things should work, so of course they will work in that way. I was wrong in a big way.

It was during a benchmark that I discovered just how wrong I was. In the early days of these databases, it was common for the vendors to 'benchmark' for really large procurements - to see who could do the work the fastest, the easiest, with the most features. It was set up between Informix, Sybase and Oracle. Oracle was first. Their technical people came on-site, read through the benchmark specs, and started setting it up. The first thing I noticed was that they were going to use a database table to record their timings, even though we were going to have many dozens of connections doing work, each of which would frequently need to insert and update data in this log table. Not only that, but they were going to read the log table during the benchmark as well! I, being a nice guy, pulled one of them aside to ask him if he was crazy - why would they purposely introduce another point of contention into the system? Wouldn't the benchmark processes all tend to serialize around their operations on this single table? Would we jam up the benchmark by trying to read from this table as others were heavily modifying it? Why would you want to introduce all of these extra locks you need to manage? I had dozens of 'why would you even consider that' - type of questions. The technical folks from Oracle thought I was a little daft at that point. That is until I pulled up a window into Sybase or Informix, and showed them the effects of two people inserting into a table, or someone trying to query a table with others inserting rows (the query returns zero rows per second). The differences between the way Oracle does it, and the way almost every other database does it, are phenomenal - they are night and day. Needless to say, neither Informix nor Sybase were too keen on the database log table approach during their attempts. They preferred to record their timings to flat files in the operating system.

The moral to this story is twofold; all databases are fundamentally different and, when designing your application, you must approach each as if you never used a database before. Things you would do in one database are either not necessary, or simply won't work in another database.

In Oracle you will learn that:

  • Transactions are what databases are all about; they are good.

  • You should defer committing as long as you have to. You should not do it quickly to avoid stressing the system, as it does not stress the system to have long or large transactions. The rule is commit when you must, and not before. Your transactions should only be as small or large as your business logic dictates.

  • You should hold locks on data as long as you need to. They are tools for you to use, not things to be avoided. Locks are not a scarce resource.

  • There is no overhead involved with row level locking in Oracle, none.

  • You should never escalate a lock (for example, use a table lock instead of row locks) because it would be 'better on the system'. In Oracle it won't be better for the system - it will save no resources.

  • Concurrency and consistency can be achieved. You can get it fast and correct, every time.

As we go through the remaining components in this chapter, we'll be reinforcing the above points.

Locking Issues

Before we discuss the various types of locks that Oracle uses, it is useful to look at some locking issues - many of which arise from badly designed applications that do not make correct use (or make no use) of the database's locking mechanisms.

Lost Updates

A lost update is a classic database problem. Simply put, a lost update occurs when the following events occur, in the order presented:

  1. User 1 retrieves (queries) a row of data.

  2. User 2 retrieves that same row.

  3. User 1 modifies that row, updates the database and commits.

  4. User 2 modifies that row, updates the database and commits.

This is called a lost update because all of the changes made in step three above will be lost. Consider, for example, an employee update screen - one that allows a user to change an address, work number and so on. The application itself is very simple - a small search screen to generate a list of employees and then the ability to drill down into the details of each employee. This should be a piece of cake. So, we write the application with no locking on our part, just simple SELECT and UPDATE commands.

So, an end user (user 1) navigates to the details screen, changes an address on the screen, hits Save, and receives confirmation that the update was successful. Fine, except that when user 1 checks the record the next day, in order to send out a tax form, the old address is still listed. How could that have happened? Unfortunately it can happen all too easily. In this case another end user (USER2) had queried the same record about 5 minutes before user 1 and still had the old data displayed on their screen. User 1 came along, queried up the data on his terminal, performed his update, received confirmation, and even re-queried to see the change for himself. However, user 2 then updated the work telephone number field and hit save - blissfully unaware of the fact that he has just overwritten user 1's changes to the address field with the old data! The reason this can happen is that the application developer, finding it easier to update all columns instead of figuring out exactly which columns changed, wrote the program such that when one particular field is updated, all fields for that record are 'refreshed'.

Notice that for this to happen, user 1 and user 2 didn't even need to be working on the record at the exact same time. All it needed was for them to be working on the record at about the same time

This is a database issue that I've seen crop up time and time again when GUI programmers, with little or no database training, are given the task of writing a database application. They get a working knowledge of SELECT, INSERT, UPDATE and DELETE and then set about writing the application. When the resulting application behaves in the manner described above, it completely destroys people's confidence in it, especially since it seems so random, so sporadic, and is totally irreproducible in a controlled environment (leading the developer to believe it must be user error).

Many tools, such as Oracle Forms, transparently protect you from this behavior by ensuring the record is unchanged from when you queried it and locked before you make any changes to it - but many others (such as a handwritten VB or Java program) do not. What the tools that protect you do behind the scenes, or what the developers must do themselves, is to use one of two types of locking.

Pessimistic Locking

This locking method would be put into action in the instant before we modify a value on screen - for example when the user selects a specific row and indicates their intention to perform an update (by hitting a button on the screen, say). So, a user queries the data out without locking:

scott@TKYTE816> SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;           EMPNO ENAME             SAL ---------- ---------- ----------       7782 CLARK            2450       7839 KING             5000       7934 MILLER           1300 

Eventually, the user picks a row they would like to update. Let's say in this case, they choose to update the MILLER row. Our application will at that point in time (before they make any changes on screen) issue the following command:

scott@TKYTE816> SELECT EMPNO, ENAME, SAL   2    FROM EMP   3   WHERE EMPNO = :EMPNO   4     AND ENAME = :ENAME   5     AND SAL = :SAL   6     FOR UPDATE NOWAIT   7  /           EMPNO ENAME             SAL ---------- ---------- ----------       7934 MILLER           1300 

What the application does is supply values for the bind variables from the data on the screen (in this case 7934, MILLER and 1300) and re-queries up this same row from the database - this time locking the row against updates by other sessions. This is why this approach is called pessimistic locking. We lock the row before we attempt to update because we doubt that the row will remain unchanged otherwise.

Since all tables have a primary key (the above SELECT will retrieve at most one record since it includes the primary key, EMPNO) and primary keys should be immutable (we should never update them) we'll get one of three outcomes from this statement:

Once we have locked the row successfully, the application will issue some update and commit the changes:

scott@TKYTE816> UPDATE EMP   2  SET ENAME = :ENAME, SAL = :SAL   3  WHERE EMPNO = :EMPNO; 1 row updated.      scott@TKYTE816> commit; Commit complete. 

We have now very safely changed that row. It is not possible for us to overwrite someone else's changes as we verified the data did not change between the time we initially read it out and when we locked it.

Optimistic Locking

The second method, referred to as optimistic locking, is to keep the old and new values in the application and upon updating the data use an update like this:

Update table    Set column1 = :new_column1, column2 = :new_column2,  .  Where column1 = :old_column1    And column2 = :old_column2       

Here, we are optimistically hoping that the data doesn't get changed. In this case, if our update updates one row - we got lucky, the data didn't change between the time we read it out and the time we got around to submitting the update. If we update zero rows, we lose - someone else changed the data and now we must figure out what we want to do in order to avoid the lost update. Should we make the end user re-key the transaction after querying up the new values for the row (potentially frustrating them no end as there is a chance the row will change yet again on them)? Should we try to merge the values of the two updates, performing update conflict resolution based on business rules (lots of code)? Of course, for disconnected users, the last option is the only one available.

It should be noted that you can use a SELECT FOR UPDATE NOWAIT here as well. The UPDATE above will in fact avoid a lost update, but it does stand a chance of blocking - hanging while it waits for an UPDATE of that row by another session to complete. If all of your applications use optimistic locking then using a straight UPDATE is generally OK, rows are locked for a very short duration as updates are applied and committed. If some of your applications use pessimistic locking, however, which will hold locks on rows for relatively long periods, then you would want to consider using a SELECT FOR UPDATE NOWAIT immediately prior to the UPDATE to avoid getting blocked by another session.

So, which method is best? In my experience, pessimistic locking works very well in Oracle (but perhaps not other databases) and has many advantages over optimistic locking.

With pessimistic locking the user can have confidence that the data they are modifying on the screen is currently 'owned' by them - they in effect have the record checked out and nobody else can modify it. Some would argue that if you lock the row before changes are made, other users would be locked out from that row and the scalability of the application would be decreased. The fact is that, however you do it, only one user will ultimately be able to update the row (if we want to avoid the lost update). If you lock the row first, and then update it, your end user has a better experience. If you don't lock it and try to update it later your end user may put time and energy into making changes only to be told 'sorry, the data has changed, please try again'. To limit the time that a row is locked before updating, you could have the application release the lock if the user walks away and doesn't actually use the record for some period of time or use Resource Profiles in the database to time out idle sessions.

Furthermore, locking the row in Oracle does not prevent reads of that record as in other databases; the locking of the row does not prevent any normal activity from taking place. This is due 100% to Oracle's concurrency and locking implementation. In other databases, the converse is true. If I tried to do pessimistic locking in them, no application would work. The fact that a locked row in those databases block queries prevents this approach from even being considered. So, it may be necessary to unlearn 'rules' you have learned in one database in order to be successful in a different database.

Blocking

Blocking occurs when one session holds a lock on a resource that another session is requesting. As a result, the requesting session will be blocked, it will 'hang' until the holding session gives up the locked resource. In almost every case, blocking is avoidable. In fact, if you do find yourself blocking in an interactive application you are most likely suffering from the lost update bug described above (your logic is flawed and that is the cause of the blocking).

There are four common DML statements that will block in the database - INSERT, UPDATE, DELETE, and SELECT FOR UPDATE. The solution to a blocked SELECT FOR UPDATE is trivial: simply add the NOWAIT clause and it will no longer block. Instead, your application would report back to the end user that the row is already locked. The interesting cases are the remaining three DML statements. We'll look at each of them and see why they should not block, and when they do - how to correct that.

Blocked Inserts

The only time an INSERT will block is when you have a table with a primary key or unique constraint placed on it and two sessions simultaneously attempt to insert a row with the same value. One of the sessions will block until the other session either commits (in which case the blocked session will receive an error about a duplicate value) or rolls back (in which case the blocked session succeeds). This typically happens with applications that allow the end user to generate the primary key/unique column value. It is most easily avoided via the use of Oracle sequences in the generation of primary keys as they are a highly concurrent method of generating unique keys in a multi-user environment. In the event that you cannot use a sequence, you can use the technique outlined in Appendix A, on the DBMS_LOCK package, where I demonstrate how to use manual locks to avoid this issue.

Blocked Updates and Deletes

In an interactive application - one where you query some data out of the database, allow an end user to manipulate it and then 'put it back' into the database, a blocked UPDATE or DELETE indicates that you probably have a lost update problem in your code. You are attempting to UPDATE a row that someone else is already updating, in other words that someone else already has locked. You can avoid the blocking issue by using the SELECT FOR UPDATE NOWAIT query to:

As discussed earlier, you can do this regardless of the locking approach you take - both pessimistic and optimistic locking may employ the SELECT FOR UPDATE NOWAIT to verify the row has not changed. Pessimistic locking would use that statement the instant the user indicated their intention to modify the data. Optimistic locking would use that statement immediately prior to updating the data in the database. Not only will this resolve the blocking issue in your application, it will also correct the data integrity issue.

Deadlocks

Deadlocks occur when two people hold a resource that the other wants. For example, if I have two tables, A and B in my database, and each has a single row in it, I can demonstrate a deadlock easily. All I need to do is open two sessions (two SQL*PLUS sessions, for example), and in Session A, I update table A. In session B, I update table B. Now, if I attempt to update table A in session B, I will become blocked. Session A has this row locked already. This is not a deadlock, this is just blocking. We have not yet deadlocked since there is a chance that the session A will commit or rollback, and session B will simply continue at that point.

If we go back to session A, and then try to update table B, we will cause a deadlock. One of the two sessions will be chosen as a 'victim', and will have its statement rolled back. For example, the attempt by session B to update table A may be rolled back, with an error such as:

update a set x = x+1        * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource 

Session A's attempt to update table B will remain blocked - Oracle will not rollback the entire transaction. Only one of the statements that contributed to the deadlock is rolled back. Session B still has the row in table B locked, and session A is patiently waiting for the row to become available. After receiving the deadlock message, session B must decide whether to commit the outstanding work on table B, roll it back, or continue down an alternate path and commit later. As soon as this session does commit or rollback, the other blocked session will continue on as if nothing ever happened.

Oracle considers deadlocks to be so rare, so unusual, that it creates a trace file on the server each and every time one does occur. The contents of the trace file will look something like this:

*** 2001-02-23 14:03:35.041 *** SESSION ID:(8.82) 2001-02-23 14:03:35.001 DEADLOCK DETECTED Current SQL statement for this session: update a set x = x+1 The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following... 

Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one.

The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:

So, as a demonstration of the first point, if I have a pair of tables set up such as:

tkyte@TKYTE816> create table p ( x int primary key ); Table created.      tkyte@TKYTE816> create table c ( y references p ); Table created.      tkyte@TKYTE816> insert into p values ( 1 ); tkyte@TKYTE816> insert into p values ( 2 );      tkyte@TKYTE816> commit; 

And then I execute:

tkyte@TKYTE816> update p set x = 3 where x = 1; 1 row updated. 

I will find that my session has locked the table C. No other session can delete, insert or update any rows in C. Again, updating a primary key is a huge 'no-no' in a relational database, so this is generally not really an issue. Where I have seen this updating of the primary key become a serious issue is when you use tools that generate your SQL for you and those tools update every single column - regardless of whether the end user actually modified that column or not. For example, if you use Oracle Forms and create a default layout on any table Oracle Forms by default will generate an update that modifies every single column in the table you choose to display. If you build a default layout on the DEPT table and include all three fields Oracle Forms will execute the following command whenever you modify any of the columns of the DEPT table:

update dept set deptno=:1,dname=:2,loc=:3 where rowid=:4 

In this case, if the EMP table has a foreign key to DEPT and there is no index on the DEPTNO column in the EMP table - the entire EMP table will be locked after an update to DEPT. This is something to watch out for carefully if you are using any tools that generate SQL for you. Even though the value of the primary key does not change, the child table EMP will be locked after the execution of the above SQL statement. In the case of Oracle Forms, the solution is to mark that table's property update changed columns only to Yes. Oracle Forms will generate an update statement that includes only the changed columns (not the primary key).

Problems arising from deletion of a row in a parent table are far more common. If I delete a row in table P, then the child table, C, will become locked - preventing other updates against C from taking place for the duration of my transaction (assuming no one else was modifying C, of course; in which case my delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table C, I have seriously decreased the concurrency in my database - no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now 'own' lots of data until I commit. The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, I'll start seeing lots of sessions that hold some pre-existing locks getting blocked in the database. If any of these blocked sessions are, in fact, holding a lock that my session needs - we will have a deadlock. The deadlock in this case is caused by my session obtaining many more locks then it ever needed. When someone complains of deadlocks in the database, I have them run a script that finds un-indexed foreign keys and ninety-nine percent of the time we locate an offending table. By simply indexing that foreign key, the deadlocks, and lots of other contention issues, go away. Here is an example of how to automatically find these un-indexed foreign keys:

tkyte@TKYTE816> column columns format a30 word_wrapped tkyte@TKYTE816> column tablename format a15 word_wrapped tkyte@TKYTE816> column constraint_name format a15 word_wrapped      tkyte@TKYTE816> select table_name, constraint_name,   2       cname1 || nvl2(cname2,','||cname2,null) ||   3       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||   4       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||   5       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)   6              columns   7    from ( select b.table_name,   8                  b.constraint_name,   9                  max(decode( position, 1, column_name, null )) cname1,  10                  max(decode( position, 2, column_name, null )) cname2,  11                  max(decode( position, 3, column_name, null )) cname3,  12                  max(decode( position, 4, column_name, null )) cname4,  13                  max(decode( position, 5, column_name, null )) cname5,  14                  max(decode( position, 6, column_name, null )) cname6,  15                  max(decode( position, 7, column_name, null )) cname7,  16                  max(decode( position, 8, column_name, null )) cname8,  17                  count(*) col_cnt  18             from (select substr(table_name,1,30) table_name,  19                          substr(constraint_name,1,30) constraint_name,  20                          substr(column_name,1,30) column_name,  21                          position  22                     from user_cons_columns ) a,  23                  user_constraints b  24            where a.constraint_name = b.constraint_name  25              and b.constraint_type = 'R'  26            group by b.table_name, b.constraint_name  27         ) cons  28   where col_cnt > ALL  29           ( select count(*)  30               from user_ind_columns i  31              where i.table_name = cons.table_name  32                and i.column_name in (cname1, cname2, cname3, cname4,  33                                      cname5, cname6, cname7, cname8 )  34                and i.column_position <= cons.col_cnt  35              group by i.index_name  36           )  37  /      TABLE_NAME                     CONSTRAINT_NAME COLUMNS ------------------------------ --------------- ---------------------------- C                              SYS_C004710     Y 

This script works on foreign key constraints that have up to 8 columns in them (if you have more than that, you probably want to rethink your design). It starts by building an inline view, named CONS in the above query. This inline view transposes the appropriate column names in the constraint from rows into columns, the result being a row per constraint and up to 8 columns that have the names of the columns in the constraint. Additionally there is a column, COL_CNT, which contains the number of columns in the foreign key constraint itself. For each row returned from the inline view we execute a correlated subquery that checks all of the indexes on the table currently being processed. It counts the columns in that index that match columns in the foreign key constraint and then groups them by index name. So, it generates a set of numbers, each of which is a count of matching columns in some index on that table. If the original COL_CNT is greater than all of these numbers then there is no index on that table that supports that constraint. If COL_CNT is less than all of these numbers then there is at least one index that supports that constraint. Note the use of the NVL2 function (new to Oracle 8.15), which we used to 'glue' the list of column names into a comma-separated list. This function takes three arguments A, B, C. If argument A is not null then it returns argument B, else it returns argument C. This query assumes that the owner of the constraint is the owner of the table and index as well. If another user indexed the table, or the table is in another schema, it will not work correctly (both rare events).

So, this script shows us that table C has a foreign key on the column Y, but no index. By indexing Y, we can remove this locking issue all together. In addition to this table lock, an un-indexed foreign key can also be problematic in the following cases:

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

If you satisfy all three above, feel free to skip the index - it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to 'over-lock' data.

Lock Escalation

When lock escalation occurs, the system is decreasing the granularity of your locks. An example would be the database system turning your 100 row-level locks against a table into a single table-level lock. You are now using 'one lock to lock everything' and, typically, you are also locking a whole lot more data than you were before. Lock escalation is used frequently in databases that consider a lock to be a scarce resource, overhead to be avoided.

Important 

Oracle will never escalate a lock. Never.

Oracle never escalates locks, but it does practice lock conversion, or lock promotion - terms that are often confused with lock escalation.

Note 

The terms 'lock conversion' and 'lock promotion' are synonymous - Oracle typically refers to the process as conversion.

It will take a lock at the lowest level possible (the least restrictive lock possible) and will convert that lock to a more restrictive level. For example, if you select a row from a table with the FOR UPDATE clause, two locks will be created. One lock is placed on the row(s) you selected (and this will be an exclusive lock, no one else can lock that specific row in exclusive mode). The other lock, a ROW SHARE TABLE lock, is placed on the table itself. This will prevent other sessions from placing an exclusive lock on the table and thus will prevent them from altering the structure of the table, for example. All other statements against the table are permitted. Another session can even come in and make the table read-only using LOCK TABLE X IN SHARE MODE, preventing modifications. In reality, however, this other session cannot be allowed to prevent the modification that is already taking place. So, as soon as the command to actually update that row is issued, Oracle will convert the ROW SHARE TABLE lock into the more restrictive ROW EXCLUSIVE TABLE lock and the modification will proceed. This lock conversion happens transparently.

Lock escalation is not a database 'feature'. It is not a desired attribute. The fact that a database supports lock escalation implies there is some inherent overhead in its locking mechanism, that there is significant work performed to managed hundreds of locks. In Oracle the overhead to have one lock or a million locks is the same - none.

Types of Lock

The five general classes of locks in Oracle are listed below. The first three are common (used in every Oracle database) and the last two are unique to OPS (Oracle Parallel Server). We will introduce the OPS-specific locks, but will concentrate on the common locks:

We will now take a more detailed look at the specific types of locks within each of these general classes and implications of their use. There are more lock types than I can cover here. The ones I am covering are the most common ones and are the ones that are held for a long duration. The other types of lock are generally held for very short periods of time.

DML Locks

DML locks are used to ensure only one person at a time modifies a row, and that no one can drop a table upon which you are working. Oracle will place these locks for you, more or less transparently, as you do work.

TX - (Transaction) Locks

A TX lock is acquired when a transaction initiates its first change, and is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queueing mechanism so that other sessions can wait for the transaction to complete. Each and every row you modify or SELECT FOR UPDATE will 'point' to an associated TX lock. While this sounds expensive, it is not. To understand why, we need a conceptual understanding of where locks 'live' and how they are managed. In Oracle, locks are stored as an attribute of the data (see Chapter 2, Architecture, for an overview of the Oracle block format). Oracle does not have a traditional lock manager that keeps a big long list of every row that is locked in the system. Many other databases do it that way because, for them, locks are a scarce resource the use of which needs to be monitored. The more locks in use, the more they have to manage so it is a concern in these systems if 'too many' locks are being used.

If Oracle had a traditional lock manager, the act of locking a row would resemble:

  1. Find the address of the row you want to lock.

  2. Get in line at the lock manager (must be serialized, it is a common in-memory structure.)

  3. Lock the list.

  4. Search through the list to see if anyone else has locked this row.

  5. Create a new entry in the list to establish the fact that you have locked the row.

  6. Unlock the list.

Now that you have the row locked, you can modify it. Later, as you commit your changes you must:

  1. Get in line again.

  2. Lock the list of locks.

  3. Search through it and release all of your locks.

  4. Unlock the list.

As you can see, the more locks acquired, the more time spent on this operation, both before and after modifying the data. Oracle does not do it that way. Oracle does it more like this:

  1. Find the address of the row you want to lock.

  2. Go to the row.

  3. Lock it (waiting for it if it is already locked, unless we are using the NOWAIT option).

That's it, period. Since the lock is stored as an attribute of the data, Oracle does not need a traditional lock manager. Our transaction will simply go to the data and lock it (if it is not locked already). The interesting thing is that the data may appear locked when we get to it, even if it is not. When we lock rows of data in Oracle a transaction ID is associated with the bock containing the data and when the lock is released, that transaction ID is left behind. This transaction ID is unique to our transaction, and represents the rollback segment number, slot, and sequence number. We leave that on the block that contains our row to tell other sessions that 'we own this data' (not all of the data on the block, just the one row we are modifying). When another session comes along, it sees the lock ID and, using the fact that it represents a transaction, it can quickly see if the transaction holding the lock is still active. If it is not active, the data is theirs. If it is still active, that session will ask to be notified as soon as it finishes. Hence, we have a queueing mechanism: the session requesting the lock will be queued up waiting for that transaction to complete and then it will get the data.

Here is a small example showing how this happens. We will use three V$ tables in order to see how this works:

First, let's start a transaction:

tkyte@TKYTE816> update dept set deptno = deptno+10; 4 rows updated.      

Now, let's look at the state of the system at this point:

tkyte@TKYTE816> select username,   2         v$lock.sid,   3         trunc(id1/power(2,16)) rbs,   4         bitand(id1,to_number('ffff','xxxx'))+0 slot,   5         id2 seq,   6         lmode,   7         request   8  from v$lock, v$session   9  where v$lock.type = 'TX'  10    and v$lock.sid = v$session.sid  11    and v$session.username = USER  12  /      USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST -------- ---------- ---------- ---------- ---------- ---------- ---------- TKYTE             8          2         46        160          6          0      tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN   2    from v$transaction   3  /          XIDUSN    XIDSLOT     XIDSQN ---------- ---------- ----------          2         46        160 

The interesting points to note here are:

Now I'll start another session using the same user name, update some rows in EMP, and then try to update DEPT:

tkyte@TKYTE816> update emp set ename = upper(ename); 14 rows updated.      tkyte@TKYTE816> update dept set deptno = deptno-10; 

I am now blocked in this session. If we run the V$ queries again, we see:

tkyte@TKYTE816> select username,   2         v$lock.sid,   3             trunc(id1/power(2,16)) rbs,   4             bitand(id1,to_number('ffff','xxxx'))+0 slot,   5             id2 seq,   6         lmode,   7             request   8  from v$lock, v$session   9  where v$lock.type = 'TX'  10    and v$lock.sid = v$session.sid  11    and v$session.username = USER  12  /      USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST -------- ---------- ---------- ---------- ---------- ---------- ---------- TKYTE             8          2         46        160          6          0 TKYTE             9          2         46        160          0          6 TKYTE             9          3         82        163          6          0      tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN   2    from v$transaction   3  /          XIDUSN    XIDSLOT     XIDSQN ---------- ---------- ----------          3         82        163          2         46        160 

What we see here is that a new transaction has begun, with a transaction ID of (3,82,163). Our new session, SID=9, has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a request for an exclusive lock. The interesting thing to note here is that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with SID=8 is blocking the transaction with SID=9. We can see this more explicitly simply by doing a self-join of V$LOCK:

tkyte@TKYTE816> select            (select username from v$session where sid=a.sid) blocker,   2         a.sid,   3        ' is blocking ',   4         (select username from v$session where sid=b.sid) blockee,   5             b.sid   6    from v$lock a, v$lock b   7   where a.block = 1   8     and b.request > 0   9     and a.id1 = b.id1  10     and a.id2 = b.id2  11  /      BLOCKER         SID 'ISBLOCKING'  BLOCKEE         SID -------- ---------- ------------- -------- ---------- TKYTE             8  is blocking  TKYTE             9 

Now, if we commit our original transaction, SID=8, and rerun our query, we find that the request row has gone:

tkyte@TKYTE816> select username,   2         v$lock.sid,   3             trunc(id1/power(2,16)) rbs,   4             bitand(id1,to_number('ffff','xxxx'))+0 slot,   5             id2 seq,   6         lmode,   7             request, block   8  from v$lock, v$session   9  where v$lock.type = 'TX'  10    and v$lock.sid = v$session.sid  11    and v$session.username = USER  12  /      USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST -------- ---------- ---------- ---------- ---------- ---------- ---------- TKYTE             9          3         82        163          6          0      tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN   2    from v$transaction   3  /          XIDUSN    XIDSLOT     XIDSQN ---------- ---------- ----------          3         82        163 

The request row disappeared the instant the other session gave up its lock. That request row was the queuing mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed.

There are infinitely more 'pretty' displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is very useful.

However, before we can say that we have a good understanding of how the row locking in Oracle works we must look at one last piece, that is, how the locking and transaction information is managed with the data itself. It is part of the block overhead. In Chapter 2, Architecture, we discussed how the basic format of a block included some leading 'overhead' space in which to store a transaction table for that block. This transaction table contains an entry for each 'real' transaction that has locked some data in that block. The size of this structure is controlled by two physical attribute parameters on the CREATE statement for an object:

So, each block starts life with, by default, one or two transaction slots. The number of simultaneous active transactions that a block can ever have is constrained by the value of MAXTRANS, and by the availability of space on the block. You may not be able to achieve 255 concurrent transactions on the block if there is not sufficient space to grow this structure.

We can artificially demonstrate how this works by creating a table with a constrained MAXTRANS. For example:

tkyte@TKYTE816> create table t ( x int ) maxtrans 1; Table created.      tkyte@TKYTE816> insert into t values ( 1 ); 1 row created.      tkyte@TKYTE816> insert into t values ( 2 ); 1 row created.      tkyte@TKYTE816> commit; Commit complete. 

Now, in one session we issue:

tkyte@TKYTE816> update t set x = 3 where x = 1; 1 row updated. 

and in another:

tkyte@TKYTE816> update t set x = 4 where x = 2; 

Now, since those two rows are undoubtedly on the same database block and we set MAXTRANS (the maximum degree of concurrency for that block) to one, the second session will be blocked. This demonstrates what happens when more than MAXTRANS transactions attempt to access the same block simultaneously. Similarly, blocking may also occur if the INITRANS is set low and there is not enough space on a block to dynamically expand the transaction. In most cases the defaults of 1 and 2 for INITRANS is sufficient as the transaction table will dynamically grow (space permitting), but in some environments you may need to increase this setting to increase concurrency and decrease waits. An example of when you might need to do this would be a table, or even more frequently, on an index (since index blocks can get many more rows on them than a table can typically hold) that is frequently modified. We may need to increase INITRANS to set aside ahead of time sufficient space on the block for the number of expected concurrent transactions. This is especially true if the blocks are expected to be nearly full to begin with, meaning there is no room for the dynamic expansion of the transaction structure on the block.

TM - (DML Enqueue) Locks

These locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If they attempt to perform DDL on the table while you have a TM lock on it, they will receive the following error message:

drop table dept            * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified 

This is a confusing messag, at first, since there is no method to specify NOWAIT or WAIT on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be blocked, but the operation does not permit blocking. As we've seen before, it is the same message that you get if you issue a SELECT FOR UPDATE NOWAIT against a locked row.

Below, we see how these locks would appear to us in the V$LOCK table:

tkyte@TKYTE816> create table t1 ( x int ); Table created.      tkyte@TKYTE816> create table t2 ( x int ); Table created.      tkyte@TKYTE816> insert into t1 values ( 1 ); 1 row created.      tkyte@TKYTE816> insert into t2 values ( 1 ); 1 row created.      tkyte@TKYTE816> select username,   2         v$lock.sid,   3             id1, id2,   4         lmode,   5             request, block, v$lock.type   6  from v$lock, v$session   7  where v$lock.sid = v$session.sid   8    and v$session.username = USER   9  /      USERNAME        SID        ID1        ID2   LMODE    REQUEST      BLOCK TY -------- ---------- ---------- ---------- ------- ---------- ---------- -- TKYTE             8      24055          0       3          0          0 TM TKYTE             8      24054          0       3          0          0 TM TKYTE             8     327697        165       6          0          0 TX      tkyte@TKYTE816> select object_name, object_id from user_objects;      OBJECT_NAME                     OBJECT_ID ------------------------------ ---------- T1                                  24054 T2                                  24055 

Whereas we only get one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DML-locked object so it easy to find the object on which the lock is being held.

An interesting aside to the TM lock: the total number of TM locks allowed in the system is configurable by you (for details, see the DML_LOCKS init.ora parameter definition in the Oracle8i Server Reference manual). It may in fact be set to zero. This does not mean that your database becomes a read-only database (no locks), but rather that DDL is not permitted. This is useful in very specialized applications, such as OPS, to reduce the amount of intra-instance coordination that would otherwise take place. You can also remove the ability to gain TM locks on an objectbyobject basis, using the ALTER TABLE TABLENAME DISABLE TABLE LOCK command.

DDL Locks

DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTER TABLE T, the table T will have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table. DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). It is for this reason that DDL always commits in Oracle. Every CREATE, ALTER, and so on, statement is really executed as shown in this pseudo-code:

Begin    Commit;    DDL-STATEMENT    Commit; Exception    When others then rollback; End; 

So, DDL will always commit, even if it is unsuccessful. DDL starts by committing - be aware of this. It commits first so that if it has to rollback, it will not roll back your transaction. If you execute DDL, it'll make permanent any outstanding work you have performed, even if the DDL is not successful. If you need to execute DDL, but do not want it to commit your existing transaction, you may use an autonomous transaction (see Chapter 15, Autonomous Transactions, for further details).

There are three types of DDL locks:

Most DDL takes an exclusive DDL lock. If you issue a statement such as:

Alter table t add new_column date; 

the table T will be unavailable for modifications during the execution of that statement. The table may be queried using SELECT during this time, but most other operations will be prevented, including all DDL statements. In Oracle 8i, some DDL operations may now take place without DDL locks. For example, I can issue:

create index t_idx on t(x) ONLINE; 

The ONLINE keyword modifies the method by which the index is actually built. Instead of taking an exclusive DDL lock, preventing modifications of data, Oracle will only attempt to acquire a low-level (mode 2) TM lock on the table. This will effectively prevent other DDL from taking place, but will allow DML to occur normally. Oracle accomplishes this feat by keeping a record of modifications made to the table during the DDL statement, and applying these changes to the new index as it finishes the CREATE. This greatly increases the availability of data.

Other types of DDL take share DDL locks. These are taken out against dependent objects when you create stored, compiled objects, such as procedures and views. For example, if you execute:

Create view MyView as select *   from emp, dept  where emp.deptno = dept.deptno; 

Share DDL locks will be placed against both EMP and DEPT, while the CREATE VIEW command is being processed. We can modify the contents of these tables, but we cannot modify their structure.

The last type of DDL lock is a breakable parse lock. When our session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a referenced object is dropped or altered in some way.

A view that is invaluable for looking at this information is the DBA_DDL_LOCKS view. There is no V$ view for us to look at. The DBA_DDL_LOCKS view is built on the more mysterious X$ tables and, by default, it will not be installed in your database. You can install this and other locking views by running the CATBLOCK.SQL script found in the directory [ORACLE_HOME]/rdbms/admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example in a single user database I see:

tkyte@TKYTE816> select * from dba_ddl_locks;      session                                                            mode mode      id OWNER  NAME                           TYPE                 held reqe ------- ------ ------------------------------ -------------------- ---- ----       8 SYS    DBMS_APPLICATION_INFO          Body                 Null None       8 SYS    DBMS_APPLICATION_INFO          Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Body                 Null None       8 TKYTE  TKYTE                          18                   Null None       8 SYS    DATABASE                       18                   Null None      6 rows selected. 

These are all the objects that my session is 'locking'. I have breakable parse locks on a couple of the DBMS_* packages. These are a side effect of using SQL*PLUS; it calls DBMS_APPLICATION_INFO, for example. I may see more than one copy of various objects here - this is normal, and just means I have more than one thing I'm using in the shared pool that references these objects. It is interesting to note that in the view, the OWNER column is not the owner of the lock; rather it is the owner of the object being locked. This is why you see many SYS rows with - SYS owns these packages, but they all belong to my session.

To see a breakable parse lock in action, we will first create and run a stored procedure, P:

tkyte@TKYTE816> create or replace procedure p as begin null; end;   2  / Procedure created.      tkyte@TKYTE816> exec p      PL/SQL procedure successfully completed. 

The procedure, P, will now show up in the DBA_DDL_LOCKS view. We have a parse lock on it:

tkyte@TKYTE816> select * from dba_ddl_locks;      session                                                            mode mode      id OWNER  NAME                           TYPE                 held reqe ------- ------ ------------------------------ -------------------- ---- ----       8 TKYTE  P                              Table/Procedure/Type Null None       8 SYS    DBMS_APPLICATION_INFO          Body                 Null None       8 SYS    DBMS_APPLICATION_INFO          Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Body                 Null None       8 TKYTE  TKYTE                          18                   Null None       8 SYS    DATABASE                       18                   Null None      7 rows selected. 

We then recompile our procedure, and query the view again:

tkyte@TKYTE816> alter procedure p compile;      Procedure altered.      tkyte@TKYTE816> select * from dba_ddl_locks;      session                                                            mode mode      id OWNER  NAME                           TYPE                 held reqe ------- ------ ------------------------------ -------------------- ---- ----       8 SYS    DBMS_APPLICATION_INFO          Body                 Null None       8 SYS    DBMS_APPLICATION_INFO          Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Table/Procedure/Type Null None       8 SYS    DBMS_OUTPUT                    Body                 Null None       8 TKYTE  TKYTE                          18                   Null None       8 SYS    DATABASE                       18                   Null None      6 rows selected. 

We find that P is now missing from the view - our parse lock has been broken.

This view is useful to you, as a developer, when it is found that some piece of code won't compile in the test or development system - it hangs and eventually times out. This indicates that someone else is using it (actually running it) and you can use this view to see who that might be. The same will happen with GRANTS and other types of DDL against the object. You cannot grant EXECUTE on a procedure that is running, for example. You can use the same method as above to discover the potential blockers and waiters.

Latches and Internal Locks (Enqueues)

Latches and enqueues are lightweight serialization devices used to coordinate multi-user access to shared data structures, objects and files.

Latches are locks that are held for extremely short periods of time, for example the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool (as described in Chapter 2, Architecture). Latches are typically requested internally in a 'willing to wait' mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an 'immediate' mode, meaning that the process will go do something else rather than sit and wait for the latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the 'luck of the draw', if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters, just a 'mob' of waiters constantly retrying.

Oracle uses atomic instructions like 'test and set' for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets it. Since it is only one instruction, it can be quite fast. Latches are held for short periods of time and provide a mechanism for clean-up in case a latch holder 'dies' abnormally while holding it. This cleaning up process would be performed by PMON.

Enqueues are another, more sophisticated, serialization device, used when updating rows in a database table, fro example. They differ from latches in that they allow the requestor to 'queue up' and wait for the resource. With a latch request, the requestor is told right away whether they got the latch or not. With an enqueue, the requestor will be blocked until they actually attain it. As such, they are not as fast as a latch can be, but they do provided functionality over and above that which a latch can offer. Enqueues may be obtained at various levels, so you can have many 'share' locks and locks with various degrees of 'shareability'.

Manual Locking and User-Defined Locks

So far we have looked mostly at locks that Oracle places for us transparently. When we update a table, Oracle places a TM lock on it to prevent other sessions from dropping that table (or perform most DDL in fact). We have TX locks that are left on the various blocks we modify so others can tell what data we 'own'. The database employs DDL locks to protect objects from change while we are ourselves changing them. It uses latches and locks internally to protect its own structure. Now, let's take a look at how we can get involved in some of this locking action. Our options are:

We will briefly discuss why you might want to do each of these.

Manual Locking

We have, in fact, already seen a couple of cases where we might want to use manual locking. The SELECT...FOR UPDATE statement is the predominant method of manually locking data. We used it in previous examples in order to avoid the lost update issue, whereby one session would overwrite another session's changes. We've seen it used as a method to serialize access to detail records in order to enforce business rules (the resource scheduler example from Chapter 1, Developing Successful Oracle Applications).

We can also manually lock data using the LOCK TABLE statement. This is actually only used rarely, because of the coarseness of the lock. It simply locks the table, not the rows in the table. If you start modifying the rows they will be 'locked' as normal. So, it is not a method to save on resources (as it might be in other RDBMSs). You might use the LOCK TABLE IN EXCLUSIVE MODE statement if you were writing a large batch update that would affect most of the rows in a given table, and you wanted to be sure that no one would 'block' you. By locking the table in this manner, you can be assured that your update will be able to do all of its work without getting blocked by other transactions. It would be the rare application however that has a LOCK TABLE statement in it.

Creating your own Locks

Oracle actually exposes to developers the enqueue lock mechanism that it uses internally, via the DBMS_LOCK package (which we will cover in much more detail in Appendix A). You might be wondering why you would want to create your own locks. The answer is typically application-specific. For example, you might use this package to serialize access to some resource external to Oracle. Say you are using the UTL_FILE routine that allows you to write to a file on the server's file system. You might have developed a common message routine that every application calls to record messages. Since the file is external, Oracle won't coordinate the many users trying to modify it simultaneously. In comes the DBMS_LOCK package. Now, before you open, write, and close the file, you will request a lock named after the file in exclusive mode and after you close the file, you will manually release the lock. In this fashion, only one person at a time will be able to write a message to this file. Everyone else will queue up. The DBMS_LOCK package allows you to manually release a lock when you are done with it, or to give it up automatically when you commit, or even to keep it as long as you are logged in.

What is Concurrency Control?

Concurrency controls are the collection of functions that the database provides in order to allow many people to access and modify data simultaneously. The implementation of locking in the database is perhaps the most crucial factor in determining the degree of concurrency that your application can support (basically, how well it will scale). As we discussed previously, there are many different types of locks - from TX transaction locks, which are extremely scalable both in terms of performance and cardinality (whether you have one or one billion of them), to TM and DDL locks (applied in the least restrictive mode whenever possible), to the internal locks that Oracle employs to mediate access to its shared data structures, from the very lightweight and fast latching mechanism to the heavier, but feature-rich, enqueue.

But concurrency control goes beyond locks. There are other things that the database can do to provide controlled, yet highly concurrent, access to data. For example, there is a feature of Oracle called multi-versioning (introduced in Chapter 1). Since Oracle uses multi-versioning to provide read-consistent views of data, we get the rather pleasant side effect that a reader of data will never be blocked by a writer of data; - writes do not block reads. This is one of the fundamental differences between Oracle and the rest of the databases out there. A read query in Oracle will never be blocked, it will never deadlock with another session, and it will never get an answer that didn't exist in the database.

Oracle's multi-versioning model for read consistency is always applied at the statement level (for each and every query) and can also be applied at the transaction level. What I would like to do in this section is to demonstrate how multi-versioning ties in with the various transaction isolation levels defined in the SQL92 standard.

Transaction Isolation Levels

The ANSI/ISO SQL92 standard defines four levels of transaction isolation, with different possible outcomes for the same transaction scenario. That is, the same work performed in the same fashion with the same inputs, may result in different answers, depending on your isolation level. These isolation levels are defined in terms of three 'phenomena' that are either permitted or not at a given isolation level:

The SQL92 isolation levels are defined based on whether or not they allow each of the above phenomena:

Isolation Level

Dirty Read

Non-REPEATABLE READ

Phantom Read

READ UNCOMMITTED

Permitted

Permitted

Permitted

READ COMMITTED

 

Permitted

Permitted

REPEATABLE

     

READ

   

Permitted

SERIALIZABLE

     

Oracle explicitly supports the READ COMMITTED and SERIALIZABLE isolation levels, as they are defined in the standard. However, this doesn't tell the whole story. The SQL92 standard was attempting to set up isolation levels that would permit various degrees of consistency for queries performed in each level. REPEATABLE READ is the isolation level that they claim will guarantee a read consistent result from a query. In their definition, READ COMMITTED does not give you consistent results and READ UNCOMMITTED is the level to use to get non-blocking reads.

In Oracle, READ COMMITTED has all of the attributes required to achieve Read-consistent queries. In other databases, READ COMMITTED queries can and will return answers that never existed in the database at any point in time. Moreover, Oracle also supports the spirit of READ UNCOMMITTED. The goal of providing a dirty read is to supply a non-blocking read, whereby queries are not blocked by, and do not block, updates of the same data. However, Oracle does not need dirty reads to achieve this goal, nor does it support them. Dirty reads are an implementation other databases must use in order to provide non-blocking reads.

In addition to the four defined SQL92 isolation levels, Oracle provides another level, read only. A read-only transaction is equivalent to a read-only REPEATABLE READ or SERIALIZABLE in SQL92. It only sees those changes that were committed at the time the transaction began, but inserts, updates and deletes are not permitted in this mode (other sessions may update data, but not the read-only transaction). Using this mode you can achieve REPEATABLE READ and SERIALIZABLE READ, without phantoms.

Let's now move on to discuss exactly how multi-versioning and read consistency fits into the above isolation schemes, and how other databases that do not support multi-versioning would achieve the same results. This is instructive for anyone who has used another database and believes they understand how the isolation levels must work. It is also interesting to see how a standard that was supposed to remove the differences from the databases, SQL92, actually allows for it. The standard, while very detailed, can be implemented in very different ways.

READ UNCOMMITTED

The READ UNCOMMITTED isolation level permits for dirty reads. Oracle does not make use of dirty reads, nor does it even allow for them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that caters for non-blocking reads. As we have seen, Oracle provides for non-blocking reads by default. You would be hard-pressed to make a SELECT query block in the database (there is the special case of a distributed in-doubt transaction, which we discuss in Chapter 4). Every single query, be it a SELECT, INSERT, UPDATE, or DELETE, executes in a read-consistent fashion.

In Chapter 1, Developing building Successful Oracle Applications, Oracle's method of obtaining read-consistency was demonstrated by way of an accounts example. We're now going to revisit that example to discuss in more detail what happens in Oracle, using multi-versioning, and what would happen in any number of other databases. Again, we are assuming one database row per block.

We will start with the same basic table and query:

create table accounts ( account_number number primary key,   account_balance number );      select sum(account_balance) from accounts; 

Before the query begins, we have the following data:

Row

Account Number

Account Balance

1

123

$500.00

2

456

$240.25

...

...

...

342,023

987

$100.00

Now, our select statement starts executing and reads row 1, row 2, and so on. At some point while we are in the middle of the query, a transaction moves $400.00 from account 123 to account 987. This transaction does the two updates, but does not commit. The table now looks like this:

Row

Account Number

Account Balance

LOCKED

1

123

($500.00) changed to $100.00

X

2

456

$240.25

 

...

...

...

 

342,023

987

($100.00) changed to $500.00

X

So, two of those rows are locked - if anyone tried to update them they would be blocked. So far the behavior we are seeing is more or less consistent across all databases. The difference will be in what happens when the query gets to the locked data.

When the query we are executing gets to the locked block, it will notice that the data on it has changed since the beginning of its execution. In order to provide a consistent (correct) answer, Oracle will at this point recreate the block with the locked data as it existed when the query began. That is, Oracle takes a detour around the lock - it reads around it, reconstructing it from the rollback segment. A consistent and correct answer comes back without waiting for the transaction to commit.

Now, a database that allowed a dirty read would simply return the value it saw in account 987 at the time it read it, in this case $500. The query would count the transferred $400 twice and would present a total that never existed in the accounts table at any point in time. In a multi-user database, a dirty read can be a dangerous feature and, personally, I have never seen the usefulness of it. It not only returns the wrong answer, but it may see data that will never actually exist in the database at any point in time. Say that, rather than transferring, the transaction was actually just depositing $400 in account 987. The dirty read would count the $400 and get the 'right' answer, wouldn't it? Well, suppose the uncommitted transaction was rolled back. We have just counted $400 that was never actually in the database.

The point here is that dirty read is not a feature - rather it is a liability. In Oracle, it is just not needed. You get all of the advantages of a dirty read (no blocking) without any of the incorrect results.

READ COMMITTED

The READ COMMITTED isolation level states that a transaction may only read data that was committed before the transaction began. There are no dirty reads. There may be non- REPEATABLE READ s (re-reads of the same row return a different answer) and phantom reads (newly inserted rows become visible to a query that were not visible earlier in the transaction). READ COMMITTED is perhaps the most commonly used isolation level in database applications everywhere. It is rare to see a different isolation level used.

READ COMMITTED isolation is not as cut and dry as it sounds. If you look at the matrix above, it looks straightforward. Obviously, given the rules above, a query executed in any database using READ COMMITTED isolation would behave in the same way, would it not? It will not. If you query multiple rows in a single statement then, in almost every other database, READ COMMITTED isolation can be as bad as a dirty read, depending on the implementation.

In Oracle, using multi-versioning and read consistent queries, the answer we get from the accounts query is the same in READ COMMITTED as it was in the READ UNCOMMITTED example. Oracle will reconstruct the modified data as it appeared when the query began, returning the answer that was in the database when the query started.

Let's now take a look at how our above example might work in READ COMMITTED mode in other databases - you might find the answer surprising. We'll pick up our example at the point described in the previous table:

We know what happens in Oracle when it gets to account 987 - it will read around the modified data, find out it should be $100.00 and complete. Let's see how another database, running in some default READ COMMITTED mode, might arrive at the answer:

Time

Query

Account transfer transaction

T1

Reads row 1, sum = $500 so far.

 

T2

Reads row 2, sum = $740.25 so far.

 

T3

 

Updates row 1, puts an exclusive lock on block 1 preventing other updates and reads. Row 1 now has $100.

T4

Reads row N, sum = ....

 

T5

 

Updates row 342023, puts an exclusive lock on this block. Row now has $500.

T6

Reads row 342023, discovers that it has been modified. This session will block and wait for this block to become available. All processing on this query stops.

 

T7

 

Commits transaction.

T8

Reads row 342,023, sees $500 and presents final answer.

 

The first thing to notice is that this other database, upon getting to account 987, will block our query. This session must wait on that row until the transaction holding the exclusive lock commits. This is one reason why many people have a bad habit of committing in the middle of their transactions. Updates interfere with reads in most other databases. The really bad news in this scenario is that we are making the end user wait for the wrong answer. You still receive an answer that never existed in the database at any point in time, as with the dirty read, but this time we made them wait for the wrong answer.

The important lesson here is that various databases executing in the same, apparently safe isolation level, can, and will, return very different answers under the exact same circumstances. It is important to understand that, in Oracle, non-blocking reads are not had at the expense of correct answers. You can have your cake and eat it too, sometimes.

REPEATABLE READ

The goal of REPEATABLE READ in SQL92 is to provide an isolation level that gives consistent, correct answers, and prevents lost updates. We'll take a look at both examples, and see what we have to do in Oracle to achieve this, and what happens in other systems.

Getting a Consistent Answer

If I have a REPEATABLE READ isolation, the results from a given query must be consistent with respect to some point in time. Most databases (not Oracle) achieve REPEATABLE READs via the use of row-level, shared read locks. A shared read lock prevents other sessions from modifying data that you have read. This of course decreases concurrency. Oracle opted for the more concurrent, multi-versioning model to provide read consistent answers.

In Oracle, using multi-versioning, you get an answer that is consistent with respect to the point in time the query began execution. In other databases, using shared read locks, you get an answer that is consistent with respect to the point in time the query completes - that is, when you can get the answer at all (more on this in a moment).

In a system that employs a shared read lock to provide REPEATABLE READs, you would observe rows in a table getting locked as the query processed them. So, using the example from above, as our query reads the accounts table, it would leave shared read locks on each row:

Time

Query

Account transfer transaction

T1

Reads row 1, sum = $500 so far. Block 1 has a shared read lock on it.

 

T2

Reads row 2, sum = $740.25 so far. Block 2 has a shared read lock on it.

 

T3

 

Attempts to update row 1 but is blocked. Transaction is suspended until it can obtain an exclusive lock.

T4

Reads row N, sum = ....

 

T5

Reads row 342023, sees $100 and presents final answer.

 

T6

Commits transaction.

 

T7

 

Updates row 1, puts an exclusive lock on this block. Row now has $100.

T8

 

Updates row 342023, puts an exclusive lock on this block. Row now has $500. Commits.

This table shows that we now get the correct answer, but at the cost of physically serializing the two transactions. This is one of the side effects of shared read locks for consistent answers: readers of data will block writers of data. This is in addition to the fact that, in these systems, writers of data will block readers of data.

So, you can see how shared read locks would inhibit concurrency, but they can also cause spurious errors to occur. In this example we start with our original table but this time with the goal of transferring $50.00 from account 987, to account 123:

Time

Query

Account transfer transaction

T1

Reads row 1, sum = $500 so far. Block 1 has a shared read lock on it.

 

T2

Reads row 2, sum = $740.25 so far. Block 2 has a shared read lock on it.

 

T3

 

Updates row 342023, puts an exclusive lock on block 342023 preventing other updates and shared read locks. This row now has $50.

T4

Reads row N, sum = ....

 

T5

 

Attempts to update row 1 but is blocked. Transaction is suspended until it can obtain an exclusive lock.

T6

Attempts to read row 342023 but cannot as an exclusive lock is already in place.

 

We have just reached the classic deadlock condition. Our query holds resources the update needs, and vice versa. Our query has just deadlocked with our update transaction. One of them will be chosen as the victim and will be killed. We just spent a long time and a lot of resources only to fail, and get rolled back at the end. This is the second side effect of shared read locks: readers and writers of data can and frequently will deadlock each other.

As we have seen in Oracle, we have statement level read consistency without reads blocking writes or deadlocks. Oracle never uses shared read locks - ever. Oracle has chosen the harder to implement, but infinitely more concurrent multi-versioning scheme.

Lost Update Prevention

A common use of REPEATABLE READ would be for lost update prevention. If we have REPEATABLE READ enabled, this cannot happen. By definition, a re-read of that row in the same session will result in the same exact data being returned.

In databases other than Oracle, a REPEATABLE READ may be implemented using SELECT FOR UPDATE and shared read locks. If two users select the same row for update, both will place a shared read lock on that data. When the first user attempts to update they will be blocked. When the second user attempts to update, a deadlock will occur. This is not ideal but it does prevent the lost update.

In Oracle, if we want REPEATABLE READ, but do not actually want to physically serialize access to a table with SELECT FOR UPDATE NOWAIT (as demonstrated earlier in the chapter), we actually need to set the isolation level to SERIALIZABLE.

SERIALIZABLE encompasses the lower levels of isolation so if you can do SERIALIZABLE, you can do REPEATABLE READ

In Oracle, a SERIALIZABLE transaction is implemented so that the read-consistency we normally get at the statement level is extended to the transaction. That is, the answers to every query we will execute in our transaction is fixed at the point in time our transaction began. In this mode if we:

Select * from T; Begin dbms_lock.sleep( 60*60*24 ); end; Select * from T; 

The answers returned from T would be the same, even though we just slept for 24 hours (or we might get an ORA-1555, snapshot too old error). The isolation level would assure us these two queries would always returns the same results. Oracle does this in the same way it provided a read consistent query. It uses the rollback segments to reconstruct the data as it existed when our transaction began, instead of just when our statement began. In a SERIALIZABLE mode transaction however, if we attempt to update data and discover at that point in time the data has changed since our transaction began, we will receive an error about not being able to serialize access. We will cover this in more detail shortly.

It is clear that this is not the optimum approach for our HR application. What would happen in that application is that both users would query the data; both users would update the data on screen. The first user would save their changes, and would succeed. The second user however, would receive an error when they attempted to save their changes. They just wasted a lot of time for nothing. They must restart the transaction, receive our changes, and do it all over again. It prevented the lost update, but at the price of an annoyed end-user. However, if a situation arises where REPEATABLE READ is required, and you do not expect transactions to attempt to update the same rows, then use of the SERIALIZABLE mode is a possible solution.

SERIALIZABLE

This is generally considered the most restrictive level of transaction isolation, but provides the highest degree of isolation. A SERIALIZABLE transaction operates in an environment that makes it appear as if there are no other users modifying data in the database, the database will be 'frozen' at the point in time your query began. Your transaction sees the database consistently, at a single point in time. Side effects (changes) made by other transactions are not visible to it, regardless of how long it has been running. SERIALIZABLE does not mean that all transactions executed by the users are the same as if they were executed one right after another in a serial fashion. It does not imply that there is some serial ordering of the transactions that would result in the same outcome. This last point is a frequently misunderstood concept and a small demonstration will clear it up. The following table represents two sessions performing work over time. The database tables A and B start out empty and are created as follows:

tkyte@TKYTE816> create table a ( x int ); Table created.      tkyte@TKYTE816> create table b ( x int ); Table created. 

Now, we have the following series of events:

Time

Session 1 Executes

Session 2 Executes

0:00

Alter session set isolation_level=serializable;

 

0:01

 

Alter session set isolation_level=serializable;

0:02

Insert into a select count(*) from b;

 

0:03

 

Insert into b select count(*) from a;

0:04

Commit;

 

0:05

 

Commit;

Now, when this is all said and done - tables A and B will each have a row with the value of zero in it. If there was some 'serial' ordering of the transactions we could not possibly have both tables containing the value zero in them. If Session 1 executed before Session 2 - then table B would have a count of 1. If Session 2 executed before Session 1 - then table A would have a count of 1. As executed above, however, both tables will have a count of zero. They just executed as if they were the only transaction in the database at that point in time. No matter how many times Session 1 queried table B, the count will be the count that was committed in the database at time 0:00. Likewise, no matter how many times Session 2 queries table A, it will be the same as it was at time 0:01.

In Oracle, serializability is achieved by extending the read consistency we get at the statement level to the transaction level. Instead of results being consistent with respect to the start of a statement, they are pre-ordained at the time you begin the transaction. Pretty deep thought there - the database already knows the answer to any question you might ask it, before you ask it.

This degree of isolation comes with a price - that price is the error:

ERROR at line 1: ORA-08177: can't serialize access for this transaction 

You will get this message whenever you attempt to update a row that has changed since your transaction began. Oracle takes an optimistic approach to serialization; it gambles on the fact that the data your transaction wants to update won't be updated by any other transaction. This is typically the way it happens and the then gamble pays off, especially in OLTP type systems. If no one else updates your data during your transaction, this isolation level, which will generally decrease concurrency in other systems, will provide the same degree of concurrency as it would without SERIALIZABLE transactions. The downside to this is that you may get the ORA-08177 error if the gamble doesn't pay off. If you think about it, however, the gamble is worth the risk. If you're using SERIALIZABLE transaction, you should not be expecting to update the same information as other transactions. If you do, you should use the SELECT ... FOR UPDATE as shown above, and this will serialize the access. So, if you

then using an isolation level of SERIALIZABLE will be achievable and effective. Oracle finds this method scalable enough to run all of their TPC-Cs (an industry standard OLTP benchmark, see http://www.tpc.org/ for details). In many other implementations, you would find this being achieved with shared read locks and their corresponding deadlocks, and blocking. Here in Oracle, we do not get any blocking but we will get the ORA-08177 if other sessions change the data we want to change as well. However, we will not get it as frequently as you will get deadlocks and blocks in the other systems.

Read-Only Transactions

Read-only transactions are very similar to SERIALIZABLE transactions, the only difference being that they do not allow modifications so are not susceptible to the ORA-08177 error. Read-only transactions are intended to support reporting needs, where the contents of the report needs to be consistent with respect to a single point in time. In other systems, you would use the REPEATABLE READ, and suffer the associated affects of the shared read lock. In Oracle you will use the read-only transaction. In this mode, the output you produce in a report that uses 50 SELECT statements to gather the data, will be consistent with respect to a single point in time - the time the transaction began. You will be able to do this without locking a single piece of data anywhere.

This is achieved by using the same multi-versioning as used for individual statements. The data is reconstructed as needed from the rollback segments and presented to you as it existed when the report began. Read-only transactions are not trouble-free however. Whereas we might see an ORA-08177 in a SERIALIZABLE transaction, we might expect to see an ORA-1555 snapshot too old error with read-only transactions. This will happen on a system where other people are actively modifying the information we are reading. Their changes (undo) are recorded in the rollback segments. But rollback segments are used in a circular fashion in much the same manner as redo logs. The longer the report takes to run, the larger the chance that some undo we need to reconstruct our data won't be there anymore. The rollback segment will have wrapped around, and portion of it we need has been reused by some other transaction. At this point, you will receive the ORA-1555, and will have to start over again. The only solution to this sticky issue is to have rollback segments that are sized correctly for your system. Time and time again, I see people trying to save a few MBs of disk space by having the smallest possible rollback segments (why 'waste' space on something I don't really need?). The problem is that the rollback segments are a key component of the way the database works, and unless they are sized correctly, you will hit this error. In 12 years of using Oracle 6, 7 and 8, I can say I have never hit an ORA-1555 outside of a testing or development system. If you hit them there, you know you have not sized the rollback segments correctly and you fix it. We will revisit this issue in Chapter 5, Redo and Rollback.

Summary

In this section, we covered a lot of material that, at times, makes you scratch your head. While locking is rather straightforward, some of the side effects are not. However, it is vital that you understand these issues. For example, if you were not aware of the table lock Oracle uses to enforce a foreign key relationship when the foreign key is not indexed, then your application would suffer from poor performance. If you did not understand how to review the data dictionary to see who was locking whom, you might never figure that one out. You would just assume that the database 'hangs' sometimes. I sometimes wish I had a dollar for every time I was able to solve the 'insolvable' hanging issue by simply running the query to detect un-indexed foreign keys, and suggesting that we index the one causing the problem - I would be very rich.

We took a look at the meaning of the isolation levels set out in the SQL92 standard, and at how other databases implement their meaning, compared to Oracle. We saw that in other implementations, ones that employ read locks to provide consistent data, there is a huge trade-off between concurrency and consistency. In order to get highly concurrent access to data you would have to decrease your needs for consistent answers. In order to get consistent, correct answers - you would need to live with decreased concurrency. We saw how in Oracle that is not the case - all due to multi-versioning. This short table sums up what you might expect in a database that employs read locking versus Oracles multi-versioning:

Isolation Level

Implementation

Writes block reads

Reads block writes

Deadlock sensitive reads

Incorrect query results

Lost updates

Lock escalation or limits

READ UNCOMMITTED

Read locking

No

No

No

Yes

Yes

Yes

READ COMMITTED

(Other databases)

Yes

No

No

Yes

Yes

Yes

REPEATABLE READ

 

Yes

Yes

Yes

No

No

Yes

SERIALIZABLE

 

Yes

Yes

Yes

No

No

Yes

READ COMMITTED

Multi-versioning

No

No

No

No

No[*]

No

SERIALIZABLE

(Oracle)

No

No

No

No

No

No

[*]With select for update nowait

  

Concurrency controls, and how the database implements them, are definitely things you want to have a good grasp of. I've been singing the praises of multi-versioning and read-consistency, but like everything else in the world, it is a double-edged sword. If you don't understand that it is there and how it works, you will make errors in application design. Consider the resource scheduler example from Chapter 1. In a database without multi-versioning, and its associated non-blocking reads, the original logic employed by the program may very well have worked. However, this logic would fall apart when implemented in Oracle - it would allow data integrity to be compromised. Unless you know how it works, you will write programs that corrupt data. It is that simple.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net