When using Oracle, or other MVCC (Multi Version Concurrency Control) databases like Postgresql or MySql in combination with InnoDb, a transaction with the SERIALIZABLE isolation level doesn’t always block (a pessimistic approach) when it conflicts with another transaction. Instead a more optimistic approach is used. This approach however can lead to an abort of the transaction with the feared: “ORA-08177: can’t serialize access for this transaction”, which in essence is just an optimistic locking failure.
A SERIALIZABLE transaction in classic lock based databases, is implemented by using pessimistic locking. The transaction needs to obtain the exclusive table lock prior to accessing the table. This prevents other SERIALIZABLE transactions and non SERIALIZABLE transactions (these need to obtain the shared table lock) from executing concurrently. The consequence is that a SERIALIZABLE transaction blocks until the exclusive table lock is obtained, or when it is rolled back (eg a timeout, detection of a deadlock, etc).
In Oracle no exclusive table lock is required to implement the SERIALIZABLE isolation level. Instead Oracle is able to take a snapshot of the database and use this snapshot for the duration of the transaction (transaction level read consistency). This is realized by reading the SCN (the System Change Number: a version number that is increased when a transaction commits) when the transaction begins and using this SCN and the data in the rollback segment, to reconstruct previous versions of records. The consequence of this approach is that different transactions could see different snapshots at the same moment (hence Multi Version Concurrency Control). Therefor a SERIALIZABLE transaction is not able to see other updates (so no worries about unrepeatable reads) but also not other inserts/deletes (so no phantom reads).
In short: instead of relying on pessimistic locks to prevent isolation problems, the transaction is able to see the state of the database as it was when the transaction began, even though other transactions could have changed the database in the meanwhile! This is one of the reasons why Oracle is able to perform well under heavy load.
There is one caveat: most transactions do more than just reading (else the READ_ONLY isolation level would have been a better solution). When a transaction modifies (inserts, updates, deletes) records, these records are exclusively locked for the remaining duration of the transaction (these changes are visible inside the transaction of course), independent of the isolation level. But once a transaction ends, these locks are released and without extra protection, it could happen that a SERIALIZABLE transaction updates an older version of the record (a reconstructed one) than the last committed version. This problem is called a lost update.
Luckily Oracle protects us from lost updates with SERIALIZABLE transactions by adding an optimistic lock: when the transaction tries to update a record that has been committed after the transaction began, it knows that a different transaction has modified it and the transaction aborts with the “ORA-08177: can’t serialize access for this transaction”. Just like optimistic locking failures in normal database communication, the standard policy of dealing with this situation is to retry the transaction and hope that the transaction isn’t interfered again.
It’s funny how one of the key things I needed to remove to make my site usable is one of the first things someone else recommends adding to make Drupal sites usable - and we’re both right.
RealJenius.com for all intents and purposes is a low-traffic site. It’s certainly more than most of the sites I host for small businesses or for my family, but all-in-all, the traffic and load is low.
I suffered at my last host for many reasons, as is well documented here, but one such reason was the communication time between the database and my web server was just too high. My current configuration of RealJenius.com favors several queries, and even with caching enabled, it needs a fairly efficient transfer from the DB to the web server.
When I moved to a MediaTemple DV, my SQL server and Web server became one in the same, and now communication between MySQL and PHP is lightning-fast. Granted, this is compounded by the fact that my server is much less overwhelmed than the piece-of-garbage I was on at nightmarehost.
John & Cailin have a very good article on the steps to scale a Drupal install to higher and higher demand - and their first recommendation is to move the database off of the web server. Of course, this configuration makes sense, because if they are sharing the same hardware, they are sharing several resources, including memory, CPU cycles, and IO cycles.
The most scalable configurations, however, will also carry with them some fixed costs. There is a natural overhead in technologies like clustering, load balancing, and networked decoupling; in many ways similar to the constraints applied by Amdahl’s Law. You can’t just double the servers, and expect double the performance.
Of course, the same differences can be applied to programming - the fastest algorithms for reasonably small sets may be very resource-hungry, using gobs and gobs of memory; the fastest data structure sorting and searching can be done when the whole data tree is in memory for example. However, if that structure gets large enough, you can quickly run out of resources, exposing the fact that the algorithm is not very scalable.
It’s up to the developer to decide the trade-offs at this point - how much data is a reasonable amount, and how much scalability do you really need.
You often pay a cost for scalability, and I was paying the cost for my host’s need for scalability, and they weren’t doing anything to relieve the performance headaches their ‘scalable’ architecture was creating for their users.