NHibernate throwing violation of Primary Key exception very infrequently in production, can't reproduce with test case

Question

Trying to get my head around this one. To me, it doesn't seem possible.

My server has reported the following error occurring once or twice a day on a busy server:

PlaylistItem.create System.Data.SqlClient.SqlException
Violation of PRIMARY KEY constraint 'PK__Videos__3214EC075812160E'. Cannot insert duplicate key in object 'dbo.Videos'. The statement has been terminated.

A PlaylistItem contains a reference to a Video object. The ID of a video is pre-determined and not handled by NHibernate. Here are the mappings for my PlaylistItem and Video entities:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Streamus" namespace="Streamus.Domain">

  <class name="PlaylistItem" table="[PlaylistItems]" lazy="false" >
    <id name="Id" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="guid.comb" />
    </id>

    <property name="Title" not-null="true" />
    <property name="Sequence" not-null="true" />

    <many-to-one name="Playlist" column="PlaylistId" />

    <many-to-one name="Video" column="VideoId" not-null="true" cascade="save-update" />

  </class>

</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Streamus" namespace="Streamus.Domain">

  <class name="Video" table="[Videos]" lazy="false" mutable="false">
    <id name="Id" length="11" type="String">
      <generator class="assigned"></generator>
    </id>

    <property name="Title" not-null="true" />
    <property name="Duration" not-null="true" />
    <property name="Author" not-null="true" />
  </class>

</hibernate-mapping>

And here is the Create/Save method in question:

/// <summary>
/// This is the work for saving a PlaylistItem without the Transaction wrapper.
/// </summary>
private void DoSave(PlaylistItem playlistItem)
{            
    //  This is a bit of a hack, but NHibernate pays attention to the "dirtyness" of immutable entities.
    //  As such, if two PlaylistItems reference the same Video object -- NonUniqueObjectException is thrown even though no changes
    //  can be persisted to the database.
    playlistItem.Video = VideoDao.Merge(playlistItem.Video);

    playlistItem.ValidateAndThrow();
    playlistItem.Video.ValidateAndThrow();

    PlaylistItemDao.Save(playlistItem);
}

I don't understand how my Create method could ever throw a PK violation if I call Merge on Video before saving.

Maybe it's a race condition? Or something else? Any advice appreciated.

score 1 · Answer 1 · answered Nov 23 '13 at 18:17

I would say that the answer is hidden in these two facts:

it happens once or twice a day on a busy server
server part is during the DoSave operation doing more operations between App server - DB server

Let's have a closer look. From the description above we know, that are are many concurrent operations I mean really many during let's say a second. If we will break it into the steps:

1) The DoSave is assembling a playlistItem.Video and then
2) asking NHibernate (i.e. DB Server) to check if there is any existing Video record
3) then operation continues (on the App server) and finally 4) the whole stuff is persisted.

Because we are not using internal DB server mechanism like IDENTITY or SEQUENCE or even some HiLo stuff... we cannot be sure that:

after step 2) is executed
...
before step 4) is executed

That (in this period) did not any other concurrent thread the same. And because that thread was a bit faster... succeeded to store the Unique value - our thread is comming to late.

In this case Isolation level like Read commited is not enough. In fact, this concept (check the DB - do stuff on the App - then update the DB) will hardly be fixed. Unless it is changed significantly.

I believe that at least this gave you some clue, while it is not a simple statement answer.

score 0 · Answer 2 · edited May 23 '17 at 10:28

Please read about what exactly Merge and Save or SaveOrUpdate are doing.

In your case, I cannot see if in this line

playlistItem.Video = VideoDao.Merge(playlistItem.Video);

playlistItem.Video is already a persistent object or detached or whatever.

Anyways, Merge might persist the object or associates the transient object with the session by merging the properties to any existing reference with the PK or does a load and merges that one with the given object.

Meaning, after calling Merge Video will have a valid ID!

Now you have set PlaylistItem to cascade on save/update the Video object.

This means, Save tries to create the Video, the Video already has an ID set and it will of course fail to insert it.

NHibernate throwing violation of Primary Key exception very infrequently in production, can't reproduce with test case

2 Answers2

Linked