Database Reference
In-Depth Information
As a SQL Server 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, SQL Server provided
only for page-level locking and, since all the data tended to be inserted into the last page of nonclustered tables,
concurrent inserts by two users was simply not going to happen.
■
a SQL Server clustered table (a table that has a clustered index) is in some regard similar to, but very different
from, an oracle cluster. SQL Server used to only support page (block) level locking; if every row inserted was to go to
the “end” of the table, you would never have had concurrent inserts or concurrent transactions in that database. the
clustered index in SQL Server was used to insert rows all over the table, in sorted order by the cluster key, and as such
improved concurrency in that database.
Note
Exactly the same issue affected concurrent updates (since an
UPDATE
was really a
DELETE
followed by an
INSERT
in SQL Server). Perhaps this is why SQL Server, by default, commits or rolls back immediately after execution of each
and every statement, compromising transactional integrity in an attempt to gain higher concurrency.
So in most cases, with page-level locking, multiple users could not simultaneously modify the same table.
Compounding this 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
second was deadly—the entire database would appear to freeze. I learned a lot of bad habits as a result. I learned that
transactions were “bad” and 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 (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, you were just out of luck. Consequently, most tables were created with
page-level locking anyway, and, as with SQL Server, both row and page-level locks would stop a query in its tracks. As
a result, I found that once again I would want to commit as fast as I could. The bad habits I picked up using SQL Server
were simply reinforced 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, and bring it down I did—many times.
When I started using Oracle, I didn't really bother reading the manuals to find out how locking worked in this
particular database. 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, SQL Server, 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 I thought of course they
would
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 (around
1992/1993), it was common for the vendors to benchmark for really large procurements to see who could do the work
the fastest, the easiest, and with the most features.
The benchmark was between Informix, Sybase SQL Server, and Oracle. Oracle went first. Their technical people
came on-site, read through the benchmark specs, and started setting it up. The first thing I noticed was that the
technicians from Oracle 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! Being a nice guy, I pulled
one of the Oracle technicians aside to ask him if they were 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 they jam the benchmark by trying to read from this table as others were heavily modifying
