 |
|
EnterpriseDB Locking
Oracle Tips by Burleson Consulting
|
Locking in EnterpriseDB works much as it does in Oracle. Unlike
many databases where readers can block writers and writers can block
readers, users will never be locked out in EnterpriseDB. This has
enormous performance implications when compared to databases that do
not support the equivalent (SQL Sever and IBM
DB2
spring to mind).
EnterpriseDB uses a Multi-Version Concurrency Control. While Oracle
moves data to a rollback segment (or to Undo in later versions),
EnterpriseDB maintains an ID in each row that works as a version
ID. When you work on a record, you update that version. If you are
reading the data, you will be looking at one version of the record.
If you are updating the record, you will see the new version.
While
Oracle cleans out rollback and undo when a transaction completes,
EnterpriseDB allows the old versions to remain in the main table
hidden to most processes. A process called Vacuum cleans out this
old data. Vacuum can run during downtime or less processing
intensive periods throughout the day.
EnterpriseDB supports manual locking via the SQL LOCK command but in
normal operations this should almost never be required. You can
lock a table in shared or exclusive modes. Even when manually
locked, a Writer will not block a Reader or vice versa.
EnterpriseDB also supports two varieties of transaction isolation:
read committed and serializable. Read committed is the default.
You can switch between modes by using the SET TRANSACTION command.
A
transaction in READ COMMITTED mode, a transaction will only see data
that was committed before a query begins. If the data changes
during a query, the query will not see any changes.
In
SERIALIZABLE mode, the query would not see any changes (even if they
are committed) during the course of a transaction.
READ
COMMITTED is valid for a single statement; SERIALIZABLE is valid for
an entire transaction.
In
EnterpriseDB, you begin a transaction with the BEGIN command (this
is further discussed in Chapter 3, SQL Primer). The transaction
continues until it is committed or rolled back.
Session 1: BEGIN;
Session 2: INSERT INTO emp (empno, ename) values (8000, ‘abc’);
Session 2: COMMIT;
Session 1: SELECT * FROM emp;
Session 1: COMMIT;
If
Session 1 was in READ COMMITTED mode, it would see the emp record
inserted by Session 2.
If
Session 1 were in SERIALIZABLE mode, session 1 would not see the
record where name is “abc”. Session 1 started its transaction
before session 2 committed the “abc” record.
The
short story to Locking in EnterpriseDB is that the locking behavior
is much like Oracle. You do not need to worry about rollback or
undo because all of the snapshot activity remains in the base table.
Conclusion
This
chapter has provided you with an introduction to the EnterpriseDB
platform. We covered installing and configuring the tools and took
a brisk walk through a sampling of the screens in those tools.
You
should now have some idea of what the Management Server, Replication
Server and Migration Toolset offer you and your organization. You
should also be able to browse your EnterpriseDB, PostgreSQL and
Oracle databases using the Developer Studio.
Finally, you should have some understanding of the EnterpriseDB
architecture and the similarities and differences EnterpriseDB has
with an Oracle database. EnterpriseDB is a secure and highly
configurable database, much like Oracle. EnterpriseDB provides an
Oracle style data dictionary along with the SQL Server, ANSI
and
PostgreSQL style dictionaries.
EnterpriseDB also offers Tablespaces, Schemas and Oracle-ish locking
and transaction mode support. While the implementation of these
features is different from Oracle’s implementation, the
functionality they offer is much the same. In EnterpriseDB,
compatibility is the goal.
In the
next chapter, we cover SQL in EnterpriseDB and learn how we can
start taking advantage of the platform we covered in this chapter.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
