How are unsaved/temporary edits stored during versioned editing?

Question

In ArcGIS Desktop, I can edit versioned, Oracle tables.

I generally edit the default version, and I've registered the tables as versioned with the option to move edits to base.

I do things like:

• Start an edit session
• Insert, update and delete data
• Undo and redo edits that were made in the edit session
• Save the edits

The documentation tells me:

Edits are temporary until you choose to save and apply them permanently to your data. You can also quit an edit session without saving your changes.

When you save edits, you write them to the data source, or a database.

Source: Starting an edit session section in What is editing?

Question:

The above documentation doesn't tell me how the unsaved/temporary edits are stored until they are saved. Are they stored in RAM, a scratch table, uncommitted rows, or somewhere else?

Answer 1

In short, unsaved/temporary edits in ArcGIS geodatabase are stored in a table, specifically Delta tables also know as A (add) & D (delete) tables based on the information I'm aware of and researched.

In Esri's Registering as versioned with the option to move edits to base

If you decide to register a feature dataset, stand-alone feature class, or table as versioned with the option to move edits to base, right-click it in the Catalog tree, point to Manage, then click Register As Versioned to open the Register As Versioned dialog box. Check Register the selected objects with the option to move edits to base. Checking this option causes edits that have been saved to the DEFAULT version, whether edited directly or merged from other versions, to be saved in the base (business) tables. Edits to other versions remain in the delta tables when you save.

In addition, Esri's Versioned tables in ArcGIS for Desktop

...However, instead of creating a new copy of or modifying the original data, the geodatabase leaves the versioned table or feature class in its original form and stores any changes to that data in separate geodatabase system tables. The geodatabase tables that record version changes are referred to as the delta tables. For each table or feature class that has been versioned, two new delta tables—an adds (a) and a deletes (d) table—are created.

Which is different in the convention used by Versioned tables in an Oracle database

When you register a feature dataset, stand-alone feature class, or table as versioned, the delta tables—the adds and deletes tables—are created in the database. The delta tables record any inserts, updates, or deletes made to a versioned table or feature class at each state of the database.

As you mention in your own post, Starting an edit session

Edits are temporary until you choose to save and apply them permanently to your data.

Also, Saving edits to a version

When you start editing a version, you start working with your own representation of the version. Other users who are connected to the same version cannot see any of your changes until you save them. While you are editing, other users may be editing the same version.

This is a slide (39/43) from this Esri presentation, 2009, Multi-user Geodatabase Editing Workflows

Edits are stored in A & D file, which each edit receiving a state ID.

Then when the edits are save they are moved to base table

To add more details to the context of this response/discussion. As mentioned per the comments section for you post, versioning is used for

In order to provide support for multiple users creating and updating large amounts of geographic information in an enterprise geodatabase, ArcSDE provides an editing environment that supports concurrent multiuser editing without creating multiple copies of the data. This editing environment is called versioning.

Versioning involves recording and managing changes to a multiuser geodatabase by creating a version of the database—an alternative, independent, persistent view of the database that does not involve creating a copy of the data and supports multiple concurrent editors. Versioning can only be implemented on ArcSDE geodatabases hosted on a database management system (DBMS) platform that supports concurrent multiuser editing. Personal geodatabases, on the other hand, which support only single-user editing, do not support versioning.

In providing versioning functionality, the editing environment also supports edit sessions that typically span a number of days, the facility to undo or redo changes made to the database, "what-if" scenarios, testing and development of data models and alternative application design proposals without affecting the published database, and a facility to monitor how the data and the database have evolved over time.

ArcSDE also provides the capability to support project workflows implemented in many organizations. To better understand the particular challenges of managing concurrent multiuser access to geographic information stored in a DBMS, this section will describe ArcSDE versioning in detail, including DBMS edit transactions, workflows, and reconciling.

States are containers for changes to the database. As changes are made to a version, the changes are tagged with the appropriate state. A state has one owner, which is set to the user who creates the state. Only the owner of a state, or the ArcSDE administrator, can modify the properties of a state or delete it.

Versions exist on a network of database states. Each version of the database points to a specific state; multiple versions may point to the same state if desired. Over time, versions will move from one state to another.

Answer 2

Manually trace what the database does:

Create a test table:

1. In SQL Developer,:

    create table test_table
    (
        field1 varchar2(255)
    );
   

2. In ArcCatalog: Register the table with the Geodatabase

3. Register the table as versioned, with the option to move edits to base.

4. In SQL Developer, run this query:

Note: I need ensure that I use a connection that has access to SDE system tables. Such a connection likely won't be one that connects as the table owner (for me it's OS Authentication connection). Documentation on the system tables can be found here, here and here.

select
    a.table_name,
    a.registration_id,
    b.state_id,
    c.owner,
    c.creation_time,
    c.closing_time,
    c.parent_state_id,
    c.lineage_name
from 
    sde.table_registry a
left join 
    sde.mvtables_modified b
    on a.registration_id = b.registration_id
left join
    sde.states c
    on b.state_id = c.state_id  
where
   a.table_name = 'TEST_TABLE'

+------------+-----------------+----------+-------+---------------+--------------+-----------------+--------------+
| TABLE_NAME | REGISTRATION_ID | STATE_ID | OWNER | CREATION_TIME | CLOSING_TIME | PARENT_STATE_ID | LINEAGE_NAME |
+------------+-----------------+----------+-------+---------------+--------------+-----------------+--------------+
| TEST_TABLE |           83445 |          |       |               |              |                 |              |
+------------+-----------------+----------+-------+---------------+--------------+-----------------+--------------+

Using the above query, I can see that the table exists in the table_registry table, but there aren't any related records in the versioning tables (mvtables_modified or states tables).

Now add a row and a value (make an unsaved/temporary edit):

5. Add the table to ArcMap

6. In Arcmap: Start editing

7. Create a new row.

8. Type "A sample edit." into FIELD1.

9. Hit enter to finish the row.

Note: Do not save the edits (and do not stop editing).

---

10. In SQL Developer: re-run the select query that was mentioned in #4 above.

I now see related records in the mvtables_modified and states tables:

+------------+-----------------+----------+----------+------------------------+--------------+-----------------+--------------+
| TABLE_NAME | REGISTRATION_ID | STATE_ID |  OWNER   |     CREATION_TIME      | CLOSING_TIME | PARENT_STATE_ID | LINEAGE_NAME |
+------------+-----------------+----------+----------+------------------------+--------------+-----------------+--------------+
| TEST_TABLE |           83445 |  1501810 |  USER1   | 10/20/2017 12:41:24 PM |              |         1501809 |      1501730 |
+------------+-----------------+----------+----------+------------------------+--------------+-----------------+--------------+

Analysis/Result:

Evidently, when I create a new record, but don't yet save the edits, rows are automatically added to the mvtables_modified and states tables.

I think this indicates that @Vince was correct. Unsaved/temporary edits are indeed committed to the database.

In addition, I can look at the adds table:

select
    *
from
    user1.A83438

+----------------+----------+--------------+
|     FIELD1     | OBJECTID | SDE_STATE_ID |
+----------------+----------+--------------+
| A sample edit. |        1 |      1501810 |
+----------------+----------+--------------+

Sure enough, I see my "A sample edit." text in the adds table, again proving @Vince's point.

---

Hint: I need to ensure that I've set up the appropriate select privileges for the the test_table so that my connection can access the table (and likewise the adds table).