Sometimes, in the database, we will want to not delete stuff because the data might still be needed later. Maybe some other process in the company will have to generate an invoice by the end of the month. That’s also one of the reason in my workplace, why there won’t be any hard delete in the database except when we’re sure it’s really necessary.

One solution for this, in hibernate, is to overwrite the sql delete command using the @SqlDelete annotation:

@Entity
@Table(name = "user_table", schema = "some_schema")
@SQLDelete(sql = "UPDATE some_schema.user_table SET user_status = 9 where user_id = ?")
public abstract class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Integer id;

    @NotNull
    @Column(name = "user_name", columnDefinition = "nvarchar(40)")
    private String userName;

    @NotNull
    @Column(name = "user_status", columnDefinition = "tinyint")
    private byte userStatus;

    ...
}

Everything will be working fine after this. Usually there will also be the @Where annotation with something like @Where(clause=“user_status <> 9”), but in my case, we’re using a database view, so if the user_status is = 9, it will be automatically taken out by the database.

So once again, everything is fine, good, and works as expected. BUT, problem will arise if we add optimistic locking.

@Entity
@Table(name = "user_table", schema = "some_schema")
@SQLDelete(sql = "UPDATE some_schema.user_table SET user_status = 9 where user_id = ?")
public abstract class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Integer id;

    @NotNull
    @Column(name = "user_name", columnDefinition = "nvarchar(40)")
    private String userName;

    @NotNull
    @Column(name = "user_status", columnDefinition = "tinyint")
    private byte userStatus;

    // Optimistic locking
    @NotNull
    @Version
    @Column(name = "last_update")
    @Temporal(TemporalType.TIMESTAMP)
    private Date lastUpdate;

    ...
}

After we changed the code to include optimistic locking, when we try to delete an entry from the database, we will get some weird “TwoPhaseCoordinator.beforeCompletion - failed for com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple” bla bla, and the ultimate cause of the error will be something like “index 2 ouf of range”. I guess optimistic locking and soft delete just doesn’t like each other.

Well, at first, I tried googling around, but with no avail, then after some testing, I figured out something. So what does hibernate actually do with optimistic locking? I read somewhere that it will add something like “where last_update = the-last-known-update” to its sql command so that the command will only execute successfully if the last_update (or the version) is still the same.

So maybe you have guessed it until this point. Yes when hibernate use the sql in @SqlDelete annotation, instead of just passing the object id, it will also pass the version of the timestamp to the sql in @SqlDelete annotation. So we will need to add this second variable in the sql.

@Entity
@Table(name = "user_table", schema = "some_schema")
@SQLDelete(sql = "UPDATE marketplace.v_user SET user_status = 9 where user_id = ? and last_update = ?")
public abstract class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Integer id;

    @NotNull
    @Column(name = "user_name", columnDefinition = "nvarchar(40)")
    private String userName;

    @NotNull
    @Column(name = "user_status", columnDefinition = "tinyint")
    private byte userStatus;

    // Optimistic locking
    @NotNull
    @Version
    @Column(name = "last_update")
    @Temporal(TemporalType.TIMESTAMP)
    private Date lastUpdate;

    ...
}

After this, optimistic locking and the sql delete overwrite (a.k.a. soft delete) should become good friends already. :)