Home > DBA, NoSQL, Oracle, SQL > Day 12: The Twelve Days of SQL: Readers do not block writers; writers do not block readers

Day 12: The Twelve Days of SQL: Readers do not block writers; writers do not block readers


On the twelfth day of Christmas, my true love gave to me
Twelve drummers drumming.

Day 12: Readers do not block writers; writers do not block readers (Day 11: AWR and Statspack are a goldmine of historical performance data)

This is the last installment of the Twelve Days of SQL series. I hope that the series proves to be of some value to you. Comments and suggestions are very welcome. Happy holidays and best wishes for the coming year.

Transactions that are executed serially, one after the other, may produce different results if executed in different orders. All such results are arguably valid.

Concurrently executed transactions should produce the same results as if they were executed serially, one after the other in some order; it would be very hard to argue otherwise. This is how the isolation requirement of the “ACID” protocol is usually understood.

The following statements about “serializability” are from the Oracle Database 11g Release 1 concepts manual. Identical statements occur in earlier editions going back to Oracle7 Server Release 7.3.

To describe consistent transaction behavior when transactions run at the same time, database researchers have defined a transaction isolation model called serializability. The serializable mode of transaction behavior tries to ensure that transactions run in such a way that they appear to be executed one at a time, or serially, rather than concurrently.

The above statements can be traced to a 1995 Oracle document titled Concurrency Control, Transaction Isolation and Serializability in SQL92 and Oracle7 (Part number A33745) by Ken Jacobs (Dr. DBA) and others. You can ask Oracle Support for a copy if you cannot find it online.

To describe fully consistent transaction behavior when transactions execute concurrently, database researchers have defined a transaction isolation model called “serializability”. The intention of the serializable mode of transaction execution is to ensure that transactions execute in such a way that they appear to be executed one at a time (“serially”), rather than concurrently. … In other words, concurrent transactions executing in serializable mode are only permitted to make database changes they could have made if the transactions had been scheduled to execute one after another, in some specific order, rather than concurrently. This mode ensures that transactions move the database from one consistent state to another consistent state, because potentially harmful interactions between concurrently executing transactions are prevented.

Almost identical language occurs in the Oracle patent for Multiversion Concurrency Control (MVCC) awarded to Ken Jacobs (Dr. DBA) and others in 1999.

To describe fully consistent transaction behavior when transactions execute concurrently, database researchers have defined a transaction isolation level called “serializability.” In the serializable isolation level, transactions must execute in such a way that they appear to be executed one at a time (“serially”), rather than concurrently. … In other words, concurrent transactions executing in serializable mode are only permitted to make database changes that they could have made if the transactions had been scheduled to execute one after another, in some specific order, rather than concurrently. This mode ensures that transactions move the database from one consistent state to another consistent state by preventing potentially harmful interactions between concurrently executing transactions.

The question is how to guarantee such isolation.

If incoming transactions are queued and only one transaction is allowed to execute at a time, then the problem is eliminated.

If the database is “sharded” and there is a transaction queue for each shard, then also the problem is eliminated. This is precisely what a NoSQL database management system called VoltDB does to enforce transaction isolation. Such sharding and queuing can produce very high throughput in some cases.

Oracle’s concurrency control scheme is called “Multiversion Concurrency Control” (MVCC) in the Oracle manuals and “Snapshot Isolation” (SI) with the “First Updater Wins” rule in scientific papers. The scheme is very attractive but has a caveat as explained in the Oracle Database 11g Release 2 concepts manual:

Although Oracle Database serializable mode is compatible with SQL92 and offers many benefits compared with read-locking implementations, it does not provide semantics identical to such systems. Application designers must consider the fact that reads in Oracle Database do not block writes as they do in other systems. Transactions that check for database consistency at the application level can require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considered when applications using serializable mode are ported to Oracle Database from other environments.

The following statements are from the Oracle Database 11g Release 2 application developer’s guide:

Because Oracle Database does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Therefore, transactions that perform database consistency checks at the application level must not assume that the data they read does not change during the transaction (even though such changes are invisible to the transaction). Code your application-level consistency checks carefully, even when using SERIALIZABLE transactions.

The application developer’s guide provides the following example as an illustration:

Transactions A and B (which are either READ COMMITTED or SERIALIZABLE) perform application-level checks to maintain the referential integrity of the parent/child relationship between two tables. Transaction A queries the parent table to check that it has a row with a specific primary key value before inserting corresponding child rows into the child table. Transaction B queries the child table to check that no child rows exist for a specific primary key value before deleting the corresponding parent row from the parent table. Both transactions assume (but do not ensure) that the data they read does not change before the transaction completes.

Create the PARENT and CHILD tables as follows and insert one row into the PARENT table.

create table parent (
  parent_name varchar(8)
);

create table child (
  child_name varchar(8),
  parent_name varchar(8)
);

insert into parent values('Warbucks');

The following timeline of two concurrent transactions A and B shows that it is possible to create an orphan record.

18:25:07 TRANSACTION A> alter session set isolation_level=serializable;

Session altered.

18:25:07 TRANSACTION A> select * from parent where parent_name='Warbucks';

PARENT_N
--------
Warbucks

1 row selected.

          18:25:16 TRANSACTION B> alter session set isolation_level=serializable;
          
          Session altered.
          
          18:25:16 TRANSACTION B> select * from child where parent_name='Warbucks';
          
          no rows selected
          
18:25:19 TRANSACTION A> insert into child values ('Annie','Warbucks');

1 row created.

          18:25:21 TRANSACTION B> delete from parent where parent_name='Warbucks';
          
          1 row deleted.
          
18:25:23 TRANSACTION A> commit;

Commit complete.

          18:25:25 TRANSACTION B> commit;
          
          Commit complete.

Here are the contents of the two tables after the experiment. Annie is an orphan.😦

18:25:28 SQL> select * from parent;

no rows selected

18:25:28 SQL> select * from child;

CHILD_NA PARENT_N
-------- --------
Annie    Warbucks

1 row selected.

The solution for the above case is to use SELECT FOR UPDATE or to define a referential integrity constraint (since it will use SELECT FOR UPDATE internally). In the above example, the delete transaction will then fail with the error message “ORA-08177: can’t serialize access for this transaction.” If not using the serializable isolation level, the delete transaction will fail with the error message “ORA-02292: integrity constraint (%s.%s) violated – child record found.”

In the next example, the programmer has tried to ensure that a resource such as a projector cannot be doubly booked for the same time period. Here is the definition of the SCHEDULES table.

create table schedules(
  resource_name varchar(25),
  start_time date,
  end_time date
);

Here is the stored procedure that is used. It checks that the requested resource has not already been reserved for an overlapping time period. A time delay is used to force the problem.

create or replace procedure
resource_scheduler(
  room_name in varchar,
  new_start_time in date,
  new_end_time in date
)
is
  already_reserved integer;
begin
  already_reserved := 0;
  --
  select count(*) into already_reserved
  from schedules
  where resource_name = room_name
  and (start_time between new_start_time and new_end_time)
  or (end_time between new_start_time and new_end_time);
  --
  user_lock.sleep(600);
  --
  if (already_reserved = 0) then
    insert into schedules values (room_name,new_start_time,new_end_time);
  end if;
  --
  commit;
end;

Here is a transaction history showing that the above procedure does not prevent a resource from being double-booked for the same time period.

18:19:08 SQL> alter session set isolation_level=serializable;

Session altered.

18:19:08 SQL> exec resource_scheduler('Projector', '2005/08/31 09:00', '2005/08/31 10:00');

PL/SQL procedure successfully completed.

18:19:10 TRANSACTION B> alter session set isolation_level=serializable;

Session altered.

18:19:10 TRANSACTION B> exec resource_scheduler('Projector', '2005/08/31 09:30', '2005/08/31 10:30');

PL/SQL procedure successfully completed.

Here are the contents of the SCHEDULES table at the end of the experiment.

18:19:17 SQL> select * from schedules;

RESOURCE_NAME START_TIME       END_TIME
------------- ---------------- ----------------
Projector     2005/08/31 09:00 2005/08/31 10:00
Projector     2005/08/31 09:30 2005/08/31 10:30

2 rows selected.

The solution for the above case is to create a separate RESOURCES table and update the resource record as part of the transaction. In scientific papers, this strategy is called “materializing the conflict.” This will prevent two users from making a reservation at the same time, even if the reservation is for different time periods. One of the transactions will then fail with the error message “ORA-08177: can’t serialize access for this transaction.”

This brings me to the end of this series. I hope that you found it valuable. Happy holidays and best wishes for the coming year.

Day 11: AWR and Statspack are a goldmine of historical performance data

John Denver and the Muppets singing Twelve Days of Christmas in a 1979 Christmas television show

Categories: DBA, NoSQL, Oracle, SQL
  1. Omotosho Taofeeq Kayode
    December 28, 2011 at 8:33 am

    It’s a challenge. Oracle is really as the name implies

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: