Database deadlock when firing triggers

Question

I use threading and when I commit data I get trigger deadlock. How to prevent database deadlock? Trigger check if row exist and if exist then do rollback..

I read this but it is not helpful much:

http://www.sql-server-performance.com/2006/deadlocks/

http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

How to avoid this error?

code:

advertismentsDao.Save(ad);
advertismentsDao.CommitChanges();

and commit fires this trigger

ALTER TRIGGER [dbo].[TRG_ViolateAdvertisements] ON [dbo].[Advertisements]
AFTER INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT 1 FROM INSERTED ins JOIN dbo.Advertisements ad
                   ON ISNULL(ins.Price, 0) = ISNULL(ad.Price, 0)
                   AND ISNULL(ins.HollidayDuration, 0) = ISNULL(ad.HollidayDuration, 0)
                   AND ISNULL(ins.Name, 0) = ISNULL(ad.Name, 0)
                   AND ISNULL(ins.Description, 0) = ISNULL(ad.Description, 0)
                   AND ISNULL(ins.Cities_idCities, 0) = ISNULL(ad.Cities_idCities, 0)
                   AND ISNULL(ins.Areas_idAreas, 0) = ISNULL(ad.Areas_idAreas, 0)
                   AND ISNULL(ins.Countries_idCountries, 0) = ISNULL(ad.Countries_idCountries, 0)
                   AND ins.Agencies_idAgencies = ad.Agencies_idAgencies
                   --AND ins.Url = ad.Url
                   HAVING COUNT(1) > 1)
   BEGIN
       RAISERROR ('Unique constraint violation in dbo.Advertisements', 16, 1)
       ROLLBACK TRAN;
   END
END

But I get error:

Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(IDbCommand insert, ISessionImplementor session)
at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)

There is a large amount of information already available about analyzing and preventing deadlocks, including in the SQL Server documentation: msdn.microsoft.com/en-us/library/ms188246.aspx msdn.microsoft.com/en-us/library/ms191242.aspx What exactly is your question? You only said that the information you found is not helpful, but you didn't explain why. — Pondlife
Why are you trying to enforce a unique constraint via a trigger rather than a unique constraint? — Martin Smith

Ben Ben · Accepted Answer · 2012-02-01T12:53:24

Use a unique constraint instead.

example:

Alter Table Advertisements
ADD Constraint Advertisements_UQ1 Unique (
   Price,
   HollidayDestination,
   Name,
   Description,
   Cities_idCities,
   Areas_idAreas,
   Countries_idCountries,
   Agencies_idAgencies
)

Database deadlock when firing triggers

1 Answers