Hello, I have a simple table like this:

CREATE TABLE [dbo].[Notifications](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Fk_institutionId] [int] NOT NULL,
    [Fk_userId] [int] NOT NULL,
    [Read] [bit] NOT NULL,
    [CategoryId] [int] NOT NULL,
    [Title] [nvarchar](150) NULL,
    [CreateDate] [datetime] NOT NULL,
    [ReadDate] [datetime] NULL,
    [DisplayDate] [datetime] NULL,
    [ReadBy] [nvarchar](100) NULL,
    [NotificationText] [nvarchar](2000) NULL,
 CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

On the master page of my application (which is loaded on each page off the web app when you are logged in), I get the notifications that were showed to the user yet by doing the following code.

CollectionClasses.NotificationsCollection notifications = GetNotifications(userId, null, null, null, null, null, true, "", NotificationRptType.Unread);

            #region update undisplayed notification as displayed

            EntityClasses.NotificationsEntity newNotification = new EntityClasses.NotificationsEntity();
            newNotification.DisplayDate = DateTime.Now;

            //filter
            IPredicateExpression filter = new PredicateExpression();
            filter.Add(CdrlShared.HelperClasses.NotificationsFields.FkUserId == userId);
            filter.Add(CdrlShared.HelperClasses.NotificationsFields.DisplayDate == DBNull.Value);

            notifications.UpdateMulti(newNotification, filter);

            #endregion

            return notifications;

Here is the method for GetNoticatification:

public static CollectionClasses.NotificationsCollection GetNotifications(int userId, int? category, DateTime? dateFrom, DateTime? dateTo,
            bool? unreadOnly, bool? readOnly, bool? undisplayedOnly, string containsWords, NotificationRptType ContainerType)
        {
            CollectionClasses.NotificationsCollection notifications = new CollectionClasses.NotificationsCollection();

            #region predicate expression

            IPredicateExpression filter = new PredicateExpression();
            //User ID
            filter.Add(HelperClasses.NotificationsFields.FkUserId == userId);
            //Notification category
            if (category.HasValue)
                filter.Add(HelperClasses.NotificationsFields.CategoryId == category.Value);
            //Notification From Date
            if (dateFrom.HasValue)
                filter.Add(HelperClasses.NotificationsFields.CreateDate >= dateFrom.Value);
            //Notification To Date
            if (dateTo.HasValue)
                filter.Add(HelperClasses.NotificationsFields.CreateDate <= dateTo.Value);
            //Notification Unread only
            if (unreadOnly.HasValue && unreadOnly.Value)
                filter.Add(HelperClasses.NotificationsFields.Read == false);
            //Notification read only
            else if (readOnly.HasValue && readOnly.Value)
                filter.Add(HelperClasses.NotificationsFields.Read == true);
            //Notification undisplayed only
            else if (undisplayedOnly.HasValue && undisplayedOnly.Value)
                filter.Add(HelperClasses.NotificationsFields.DisplayDate == DBNull.Value);
            //Notification contains words
            if (!string.IsNullOrEmpty(containsWords))
                filter.Add(HelperClasses.NotificationsFields.NotificationText % ("%" + containsWords.Trim() + "%"));

            #endregion

            //sort
            ISortExpression sort = new SortExpression();
            sort.Add(HelperClasses.NotificationsFields.CreateDate | SortOperator.Descending);

            int resultNumber = 0;
            if (ContainerType == NotificationRptType.Read)
                resultNumber = 50;

            notifications.GetMulti(filter, resultNumber, sort);

            return notifications;
        }

So because I do a SELECT then an UPDATE right away and we have many users navigating the app at the same time, could this raise deadlock? if so how can i avoid it?

Thank you

You can avoid deadLocks by minimizing the isolationLevel. For that you can use Hints... NoLock in specific.

In QuerySpec

In Linq

Additionally, use a transaction object where you add the collection, then do a getmulti and the update multi and then commit. In that case, you won't have the update waiting for read locks set by the select.

Other than that, I don't see how your code can deadlock.

Hi Otis,

Thank you for the comment. I am not sure to understand how to implement your comment in my code. Could you please elaborate or maybe point to an example somewhere on the web?

Thank you

Hello Walaa,

Thanks for the input. How to you specify NoLock with the LLBLGen code?

Thank you

Please check the links I've provided.

GregCSharp wrote:

Hi Otis,

Thank you for the comment. I am not sure to understand how to implement your comment in my code. Could you please elaborate or maybe point to an example somewhere on the web?

Thank you

Please see: https://www.llblgen.com/Documentation/5.2/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/SelfServicing/gencode_transactions.htm

As I am looking at the links i got from both of your links i see it points to version 5 of LLBLGen. would you have the link for version 2.6? I should have specify this first.

GregCSharp wrote:

As I am looking at the links i got from both of your links i see it points to version 5 of LLBLGen. would you have the link for version 2.6? I should have specify this first.

In this case, it's the same Basically you create a Transaction object, add the collection to it, and then fetch and update, and then commit the transaction. This assures you use the same connection for fetch and update, so read locks set by the fetch are not blocking your update.

Again, I doubt this is the cause of your deadlocks, but it's what I can advise looking at your code snippet.

Otis,

I was finally able to test that snippet to the web app. Well it did not solve my issue Would you mind to just confirm my code is actually avoiding dead locks please?

        public static CollectionClasses.NotificationsCollection GetUndisplayedNotificationsForUser(int userId)
        {
            CollectionClasses.NotificationsCollection notifications = GetNotifications(userId, null, null, null, null, null, true, "", NotificationRptType.Unread);

            Transaction tranMgr = new Transaction(System.Data.IsolationLevel.ReadCommitted, "Not");
            tranMgr.Add(notifications);

            #region update undisplayed notification as displayed

            EntityClasses.NotificationsEntity newNotification = new EntityClasses.NotificationsEntity();
            newNotification.DisplayDate = DateTime.Now;

            //filter
            IPredicateExpression filter = new PredicateExpression();
            filter.Add(HelperClasses.NotificationsFields.FkUserId == userId);
            filter.Add(HelperClasses.NotificationsFields.DisplayDate == DBNull.Value);

            notifications.UpdateMulti(newNotification, filter);

            #endregion

            tranMgr.Commit();
            tranMgr.Dispose();

            return notifications;
        }

Thank you for the review. Gregory

The fetch was not done inside the transaction.

Create the collection first, then create the Transaction, then add the collection to the Transaction then call GetNotifications and pass the collection to it, don't re-create the collection inside the GetNotifications method.

CollectionClasses.NotificationsCollection notifications = new CollectionClasses.NotificationsCollection();

Transaction tranMgr = new Transaction(System.Data.IsolationLevel.ReadCommitted, "Not");
tranMgr.Add(notifications);

GetNotifications(notifications, userId, null, null, null, null, null, true, "", NotificationRptType.Unread);

// Do the Update
// Commit the transaction.