In part two of this series published last month we covered all the gory details of what the State ID is within versioning, how state ids are generated for every edit we make in a version, how state ids are used to match up adds, deletes, and updates, and how the path from a state id back to state 0 creates the state lineage. We summed it up by explaining that a version name is simply an easy to remember text-based name for a state id/lineage.

In this month’s installment we want to take a look at what happens to the states in a database when we reconcile, post, and ultimately compress our geodatabase. If you’ve stuck with me this far you probably have a pretty good idea that there are many, many different state lineages in a geodatabase that has a lot of versioned edits. For the sake of digging into this topic, I want to use the example of a very simple geodatabase. In this geodatabase there is currently only a single version, SDE.Default:

Because there are no edits in the database the current state id of Default is pointing to 0 which indicates that it is rendering data directly from the base business tables and that the A&D tables are currently empty.

Now I am going to create a second version as a child of Default and I am going to add three feature records to this version. As we saw in the last article, each edit is assigned its own state id but when I save the edit session, all of those edits are assigned the same state id corresponding to the max state id used in the version. For simplicity we will assume this version used state ids 1, 2, and 3. So when I save this version, my geodatabase now looks like this:

Ok, now let’s take it another step further and create a third version with three more new features. These edits will get state ids 4, 5, and 6 and when I save the version the state id points to 6:

If I’ve already lost you, please go back and read the first two installments of this series where we explain what a version is and how the state ids are assigned (no offense intended.) Otherwise let’s keep going. Each of the two child versions above will have its own state lineage. Child1 will have a state lineage of 3 → 0 and Child2 will have a state lineage of 6 → 0. Please remember that these are two different lineages that meet back at the common state id of 0.

So now we want go ahead and reconcile Child1 to Default. To do this we open Child1 in ArcMap and click the reconcile button on the versioning toolbar. A reconcile operation syncs any edits that have been posted to Default down into Child1. In our above example, there are no additional edits available in Default at this time. Our next step is to post the edits in Child1 up to Default by clicking the post button on the versioning toolbar. When we do this Default is essentially synchronized with Child1. What this means behind the scenes is that both Default and Child1 point to the same state id of 3:

The other major change we see here is that Default is no longer representative of state 0. It is a common misconception that Default always represents state 0. However, in the above example we see that we have now separated out state 0 because Child2 still needs to reference the state 0 data without the posted edits that exist within Child1 and Default (i.e., state id 3). All three versions still eventually trace back to state 0, but our state tree has changed.

If you are using a versioned geodatabase you are certain to have heard about an SDE compress operation. Most of us know that a compress operation maintains the health of our database by compressing the state tree. But do you really know what that means? Many folks believe that the compress moves all posted edits from the A&D tables into the base business tables. In our example above we’ve posted edits from Child1 into Default. If we ran an SDE compress against the geodatabase in its current state, what would happen? The answer is absolutely nothing. In this case, no edits can be moved (compressed) into the base tables because the geodatabase must maintain the current state tree to enable Child2 to render state 0 plus the three edits (state ids 4, 5, and 6.) We can run the compress operation over and over again, but it won’t do a darned thing.

To put this further into perspective, we could create another 50 versions in the geodatabase, create edits, and then reconcile and post those edits into Default. Each time we post, the state id of Default will be updated to reference a lineage including the new edits. And we can run the compress after each post but it will continue to do nothing as long as Child2 is referencing state 0 directly.

Each time we create an edit, records are added to the A&D tables. And as records pile up in the A&D tables, the database has to work harder and harder to render a specific state lineage, because it always has to start with state 0 and then apply all of the edits that exist within the specific lineage. The more edits you have… the slower the database responds. Eventually your system will grind to a halt with performance that is unbearable.

We were called into a small utility a couple of years ago that only had two editors. But they had a case similar to the above scenario with a single version that was directly referencing state 0. After many months, they ended up with 45,000 edits in one of their ADD tables and the system performance was just awful. They couldn’t understand how this was possible… because they compressed every day. It’s an important lesson that hopefully makes more sense now. The compress is only effective if your state tree has been fully reconciled.

So to continue our exploration, let’s forget the extra 50 versions and get back to our original example. We have two child versions with edits and have posted Child1 to Default. Here is the same picture as above for reference:

Next, we’ll open Child2 and perform a reconcile against Default. This time there are edits that exist within Default (state id = 3) that do not exist within Child2 (state id = 6.) When we reconcile, the software essentially performs a mini edit session and moves the edits 1, 2, and 3 down into Child2.

However, these are considered new edits in Child2 and they are therefore given new state ids of 7, 8, and 9. When we then save Child2, the state id is now 9, corresponding to the final edit that was reconciled:

Our state tree has once again been modified because we reconciled down the edits from Default into Child2. Child2 will now have a state lineage of 9 → 3 → 0 whereas both Child1 and Default have a state lineage of 3 → 0. Keep in mind that there are still edits within Child2 that do not exist within Child1 or Default (edits 4, 5, and 6). BUT this reconcile operation has brought the two child versions back into a common lineage. They both traverse through state 3 to get to state 0.

In the geodatabase, all of the edits still exist in the A&D tables and no modifications have been made to the base business tables… yet.

Now we will run the compress operation once again. This time our state tree has been fully reconciled and our child versions share a common lineage from state id 3 to state id 0. This is important because it indicates that state id 3 is no longer needed because it contains edits that are common to ALL versions in the geodatabase.

When we run the compress, the software recognizes that state id 3 is obsolete. The compress then moves all of the edits associated with state id 3 (edits 1, 2, and 3) from the A&D tables into the base business tables. They become part of state 0. The compress then deletes the state with id 3 from the states table because it is no longer a referenced state.

Our state tree has now been simplified and looks like this:

The term “compress” is pretty accurate, because it has shortened the state tree by moving common edits into the base business tables and deleting the unreferenced states. Note that Default is now once again referencing state 0. Version Child2 still references state 9 because it has outstanding edits that have not been posted but the state lineage has been shorted back to 9 → 0.

As the number of edits decreases in the A&D tables, database performance goes back up and we can now go to sleep happy because we know our compress has been effective.

The concept remains exactly the same when you have 50 or more versions. As soon as you can fully reconcile all posted edits down into all child versions, you will cause intermediate states to become obsolete. Each state corresponds to edits in the A&D tables and when you compress, all of the edits corresponding to obsolete states get moved into the base business tables and all of the obsolete states get deleted from the geodatabase. I may sound like I am saying the same thing over and over again… I am. But it’s a very important concept which can make all the difference in the health of your geodatabase.

And now, I want to make a final comment about versioning conflicts. A conflict occurs when we have edited the same record and/or more specifically the same attribute on the same record in two different versions. The conflict appears after we post the first version and attempt to reconcile the second version. The reconcile operation detects the conflict and halts the reconcile operation.

I don’t want to get into how to resolve conflicts… there is plenty of existing documentation out there on that topic. The more important point is that it halts the reconcile operation on that version. And as we just reviewed, when a reconcile is not performed on even a single version it can cause all sorts of performance problems.

The result of this case is exactly the same as our example of not reconciling a version for a long period of time. This is why it is vitally important that you resolve those conflicts on a regular basis, which will allow the reconcile operation to complete and your compresses to be effective.

If you work in a large organization that typically has more than a few versions in your geodatabase at any given time it is imperative that you have a process to detect and report your conflicts regularly (daily is great). Ideally this is an automated process so you can use your manpower efficiently. You should attack the oldest outstanding conflicts first because they will represent the state ids that are holding back the compress from doing its job. It’s not important to have a geodatabase without any conflicts (in fact it’s almost impossible) but it is important to resolve the older conflicts on a regular schedule to keep the system motoring (dare I say speeding) along. If you need direction or assistance with any of this, feel free to give us a shout. We deal with it every day. Off my conflict soapbox…

In summary, we’ve covered how the reconcile, post, and compress operations affect the states in your geodatabase and why your compress may or may not be working effectively. The state tree in your geodatabase is constantly changing but with a little art and a bit more science you can master the state tree and keep it firmly rooted in your organization. I know that was way too cheesy but it’s late at night and I’m up writing about state ids! I often wonder how much separation there is between a versioning geek and a geeky member of The Lonely Island.

Anyhow… in the next article we will begin taking a look at Esri multi version views – what they are, how they work, and how to use them in your business. Until then, happy versioning.

Each time an edit is performed within an ESRI versioned geodatabase, a state id is assigned to that edit and the edited record is added to the Add and/or Delete table. To provide an example, in ArcMap I have created a version called SDE.TestVersioning that is a child of SDE.Default. Within that version I start an edit session and add two brand new records to the table shown above. The database will assign the next available state ids to my new records and add them to the appropriate Add table. In my test geodatabase the next records to help identify them:

SQL Server

select OBJECTID, Description, SDE_STATE_ID from [DATABASE].[BUSINESS DATA OWNER].A[REGID];

Returns:

As you can see from the above query, my records were added to the Add table and automatically given sequential state ids – 475075 and 475076. It should be noted that I have not yet saved my edit session in ArcMap. This allows for the undo/redo functionality within the ArcMap edit session because each edit is uniquely identified by a state id. If we now SAVE the edit session and run the same query we will see that ALL of the edits in the edit session are assigned the same state id corresponding to the highest state id used – 475076 in this case.

After SAVE of edit session:

After you have saved your edit session the undo/redo for those individual edits is no longer available within ArcMap because all of the edits now have the same state id. Next we will continue this example by deleting the first record we created above – “Test Add 1”. If we requery the Add table, nothing changes. However, if we query the Delete table we will now see a new record:

SQL Server

select * from [DATABASE].[BUSINESS DATA OWNER].D[REGID]

Returns

Let’s now compare the Delete table entry to the Add table entry. Three things should be noted here:

1. The SDE_DELETES_ROW_ID in the DELETE table is the OBJECTID of the record (unique ESRI identifier.)
2. The SDE_STATE_ID in the DELETE table corresponds to the state the record was added. In this case 475076 is the SDE_STATE_ID of the same record from our ADD table.
3. The DELETED_AT column contains the new state id that was assigned to this edit. This is a new unique state id that has not been used before, 475077, which is the next available state id in the system.

Because edits are tracked by individual states, the system can manage the visibility of each edit. In other words, if I viewed the data at state 475076 I would see the record on the map but if I viewed the data at state 475077 the record would be gone (deleted). Keep that in mind as we continue.

These matched ids allow the system to know that the record was added at state 475076 and then was subsequently deleted at state 475077. Because these edits are all being tracked by indvidual states, the system can manage the visibility of each edit in other words, if I viewed the data at state 475076 I would see the record on the map but if I viewed the data at state 475077 the record would be gone (deleted). Keep that in mind as we continue.

Finally, let’s review an update to the second record we created above. In my ArcMap session I have updated the Description value to be “Test Add 2 – Updated!”. If we review the corresponding records in the Add and Delete tables we should see a delete record corresponding to the original state id plus an add record with the new values (see part 1 of the versioning articles for more information).

The first thing to note above is that the OBJECTID / SDE_DELETES_ROW_ID is the first matched value we can use to tie these records together. Next we will examine the state ids. In the ADD table (top) we now see TWO records with the same OBJECTID but with different state ids. The first state id, 475076, shows the record as it was originally added to the geodatabase with a description of “Test Add 2”. We can then match that state id to the same SDE_STATE_ID value in the DELETE table. 475078, gives us the state id where the record was deleted. We can then match that value of 475078 back to the ADD table SDE_STATE_ID to find the corresponding new record with the updated description of “Test Add 2 – Updated!”. Therefore if we view the data at state 475078 the geodatabase would show the record on the map with the updated description of “Test Add 2 – Updated!” because it applies the edits by matching up the state ids.

In the above examples we have seen how records are added, deleted, and updated within the A&D tables in the geodatabase. It might be useful to re-read this section a few times to make sure you have a solid grasp on how this works because this is the basis of all versioning.

In these examples we noted that the geodatabase assigned the next available state id to the edits being performed in ArcMap. Next we’ll take a look at where those state ids are managed. There is a states table in your geodatabase that contains all of the current states which correspond to all of the edits that have occurred in your geodatabase. You can view the records in your states table using the following queries:

The following picture shows the table structure of the states table:

Here are the users of each of these columns:

• The state_id column captures the same state id that we’ve been reviewing in our above editing examples. It is a unique id that is created for each edit in each edit session.
• The owner is the user who performed the edit.
• The creation_time is the time the state was created which will match when the edit session was started.
• The closing_time is the time when the state was closed which will match when the edit session was saved.
• The parent_state_id is the preceding state in the state lineage – this is discussed in more detail below.
• The lineage_name is a foreign key to another table which defines the full state lineage for any given state.

Each versioned geodatabase begins with an initial state with a state id of 0. This state always exists in the geodatabase and is the ultimate parent of all of the other state records in the states table. As edits are performed and new states are created the parent_state_id value is always populated with the next logical parent state which will eventually tie back to state 0. If we review the states corresponding to our example edits above we find the following records:

Our most recent saved edit session had a state id of 475078 and this is the bottom record above. The parent_state_id of this state is 475076 and we can trace that record back to the logical parent state of 475076 which corresponds to the first time we saved in ArcMap. The parent_state_id of this state is 0 and we can trace that record back to the logical parent state of state 0 which is the base state of the geodatabase. To reiterate the point, ALL state records correspond to edit sessions and ALL states can be traced back to state 0. And follow this closely – state 0 corresponds to the records that exist in the base business tables (i.e. not the Add and Delete tables but the original business table that existed BEFORE the table was registered as versioned). The path that any state takes back to state 0 is called the state lineage. This is just like tracing your family lineage back to your great, great grandfather except that we are tracing parent states as opposed to parent people.

There can be many different state lineages in a versioned geodatabase. When we query data from the base business table (which correspond to state 0) plus all of the edits tied to a specific state lineage we get… you guessed it, drum roll please… an SDE VERSION! We use names like  SDE.TestVersioning to describe a version but all these names do for us is give us an easy to remember text-based name for a state id/lineage. This can be seen by querying the SDE versions table:

Oracle

select * from SDE.versions where name='TestVersioning';

SQL Server

select * from SDE.SDE.SDE_versions where name='TestVersioning';

Returns