BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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.


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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter