CODEWITHSUNDEEP
sqlasp.netsql-serverasp.net-mvc
Saeid Mirzaei
Saeid Mirzaei

Reputation: 1236

Pessimistic concurrency in SQL Server - Cannot release the application lock

I have an application lock for pessimistic concurrency and generate code in Asp.Net Mvc.

_ContextB.Database.ExecuteSqlCommand("Execute Sp_GetAppLock 'Code Generation', 'Exclusive'");
_ContextB.Entities.Add(entity);
_ContextB.SaveChanges();
// Generate code in a Sql Server Trigger(On Insert)

Error at: 

_ContextB.Database.ExecuteSqlCommand("Execute Sp_ReleaseAppLock 'Code Generation'");

Cannot release the application lock (Database Principal: 'public', Resource: 'Code Generation') because it is not currently held.

I have use multi context.

What is the problem?

Thanks.

Upvotes: 2

Views: 1311

Answers (1)

usr
usr

Reputation: 171216

EF by default uses a new connection for each operation. Your applock was never working because the connection was shut down right after that statement.

The best fix is to:

  1. Open the connection explicitly
  2. Wrap the context in using to make sure the connection is shut down
  3. Open an explicit transaction so that you control the transaction boundary

Consider using a lock table instead of Sp_GetAppLock. That's easier with EF. Make the table have one row and increment a dummy column to cause a write. That ensures exclusivity and observes transactional semantics.

Upvotes: 2

Related Questions