Timeout causing deadlock when more than one thread runs with outbox feature and all NHibernate persistence

[mauroservienti]

Source: https://nservicebus.desk.com/agent/case/11496

---

The referenced Desk case contains all the details, trace and logs: It seems to work through the dead locks but when these timeouts arrive it essentially slows the endpoint down to a crawl.

[mauroservienti]

@SzymonPobiega this needs you:-)

[JoeShook]

Any traction on this issue.

[lucasdluengo]

For me, the method "RemoveEntriesOlderThan" is timing out and deadlocking between processes.
The table "OutboxRecord" has duplicated indexes (OutboxRecord_Dispatched_Idx and OutboxRecord_Dispatched_Index do the same) but no index covers columns Dispatched and DispatchedAt at the same time, which is what this method needs.
An optimization of the method using a direct "delete from..." whould greatly improve performance and reduce locking on the table.

[lucasdluengo]

I was wondering... What if I use a different table for each process? Is there a configuration I can set to specify the "OutboxRecord" table name or schema?

[lucasdluengo]

I used the setting: NServiceBus/Persistence/NHibernate/default_schema to create an outboxrecord table for each queue. the problem I now face is that this schema is ignored while detecting if the table already exists, so it tries to create the table every time I start the queue (and crashes because it already exists).
Any idea?

[JoeShook]

I reported this issue but at the end of the day it was not related to outbox but rather my saga data's parent child schema definition.

As far as your crash goes I experienced the same thing with a recent release of NServiceBus.NHibernate that I did not previously experience. If you create the saga data schemas and outboxrecord schema then you will no longer have the race condition.

[lucasdluengo]

Thanks joe.
I already created the schemas and the service works ok the first time I started it. But the second time I start the service it crashes with the following error:

Schema update failed.
The following exception(s) were thrown:
System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: There is already an object named 'OutboxRecord' in the database

i worked around the problem with this line of code:

configuration.UsePersistence<NHibernatePersistence>().DisableSchemaUpdate();

But I'm looking for a more permanent solution. I don't wanna manually create the tables when I deploy to a new environment.

[JoeShook]

The thing is, someday you will have to update schema when new versions
force schema updates whether that be your application schema or
NServiceBus. So one way or another you will eventually be in the business
of updating schema. Not sure how you deploy but deployment tools such as
Octopus Deploy schema update a non issue.

On Mon, Jul 13, 2015 at 12:50 PM, Lucas D. Luengo [email protected]
wrote:

Thanks joe.
I already created the schemas and the service works ok the first time I
started it. But the second time I start the service it crashes with the
following error:
Schema update failed.
The following exception(s) were thrown:
System.AggregateException: One or more errors occurred. --->
System.Data.SqlClient.SqlException: There is already an object named
'OutboxRecord' in the database.

i worked around the problema with this line of code:

configuration.UsePersistence().DisableSchemaUpdate();

But I'm looking for a more permanent solution. I don't wanna manually
create the tables when I deploy to a new environment.

—
Reply to this email directly or view it on GitHub
#109 (comment)
.

[lucasdluengo]

Thanks again, Joe. You have been very helpfull.
Just one las question:
is there an equivalent to .DisableSchemaUpdate(); through App.config?

[JoeShook]

I am not sure. I am just a user like you. Maybe scanning the source code you can find it.

[lucasdluengo]

Thanks Anyway ;)

[SzymonPobiega]

@mauroservienti can this be closed?

[mauroservienti]

@SzymonPobiega

Thanks again, Joe. You have been very helpfull.
Just one las question:
is there an equivalent to .DisableSchemaUpdate(); through App.config?

Do we have an answer to the above?

[DavidBoike]

@mauroservienti @SzymonPobiega There doesn't really need to be one, since this would do the trick in user code:

var persistenceExtensions = busConfiguration.UsePersistence<NHibernatePersistene>();
if(ConfigurationManager.AppSettings["DisableSchemaUpdate"] == "true")
{
    persistenceExtensions.DisableSchemaUpdate();
}

[mauroservienti]

given @DavidBoike's suggestion I'd say that this can be closed.

[dmarckmann]

I have exactly the same problem as @lucasdluengo
I do not agree that it can be closed. The error on schema update shouldn't happen to begin with. Disabling schema updates is just a workaround.

If it is possible to create tables in a specific schema, which is a good thing, then it should also check for existence of these tables in within this schema.

[ramonsmits]

@dmarckmann in our conversation you mentioned that you were affected by both a locking issue as well as the schema workaround.

The locking original locking issue was caused by an incorrect saga mapping. That seems to have been resolved.

The locking issue from @lucasdluengo and @dmarckmann is about the outbox cleanup. Its a different issue but it has a relation with its solution which is described here : #143

@dmarckmann mentioned that they have manually configured a job in SQL Server to perform the cleanup to prevent the deadlocking.

The schema workaround is mentioned so each endpoint has its own outbox table which reduces the amount of record which reduces the number of records that are deleted each interval thus reducing deadlocks but this results in a startup error There is already an object named 'OutboxRecord' in the database which requires an additional workaround by disabling schema updates.

I think this issue is closed correctly but we don't have an issue yet for 'Specify schema for outbox requires . DisableSchemaUpdate()' if we have that then we have both the deadlocking on outboxrecord and outbox schema covered.