PBXT Transactional Characteristics

Let me first say that the PBXT storage engine has some great people behind it. At the users conference last April, I had a chance to meet Paul McCullagh, who created PBXT, and some of the people who work on it. They are dedicated individuals who are creating something unique.

Like the InnoDB storage engine, which is backed by the Innobase company, PBXT has a company that backs it, Primebase Technologies. This means that if needed, support can be got from the company that created the product. For enterprise companies this might be important.

The basics characteristics of PBXT:

• MVCC: Multi-version concurrency control, enables reading without locking.
• Transactional: Support for BEGIN, COMMIT and ROLLBACK and recovery on startup.
• ACID compliant: Atomic, Consistent, Isolated, Durable (once committed, changes cannot be lost).
• Row-level locking: updates use row-level locking, allowing for maximum concurrency.
• Deadlock detection: immediate notification if client processes are deadlocked.
• Referential Integrity: foreign-key support.
• Write-once: PBXT avoids double-writes by using a log-based architecture.

Much of this is the same as for the other transactional storage engines, so I won’t spend time on them. What sets PBXT apart from other storage engines is the write-once characteristic. It is worth understanding.

In a traditional transactional storage engine, all data is written twice: once to a log file and then once to a database table. This is done to ensure two parts of the ACID equation, durability and atomicity. I discussed these traits previously so I won’t cover them here.

While PBXT is transactional, it writes only once, to a log. You can think of this log as the database table, as PBXT doesn’t keep table data in the tradition sense. This can seem very strange to someone used to working with MyISAM table files or InnoDB tablespace files, but it offers a number of benefits:

• Less writing (duh!).
• Since table data is never updated, rollback never has to undo a change (instantaneous rollback)
• Since log files are written immediately, the records in the cache are never “dirty” (i.e. must be flushed before being freed).

These are only the highlights.

For systems with a large number of ongoing updates this approach is very beneficial. To those more interested in the inner workings of PBXT I would recommend Paul McCullagh’s PBXT whitepaper.

There are shortcomings (or potential ones) in PBXT. The main one is the lack of clustered indexes, the same issue that Falcon has. Another is that PBXT is fairly new. It hasn’t had extensive use in production environments. However, you should keep your eye on the PBXT engine, as it certainly looks like it has a bright future.