Save new or update with LinqToSql, transactions?

Question

I have a table with hit statistics for some items in another table.

This table is defined like this

ID (Primary key)
ItemId (Foreign key)
Date
Hits

Giving me have a record pr. item pr day.

The database is used in a multithreaded environment, so two users might make the first hit at the same time causing two records to be created, where I want only 1 with hits=2. (because of a unique index I would get an exception instead)

How do I ensure that I only have one record pr. item pr. day?

Answer 1

One way to handle this is by the index you create in SQL Server. With SQL Server, you can create a Unique Index but instead of having it throw an exception when you attempt to add a duplicate, it simply ignores the duplicate. To do this, make sure the index type is 'Index' and set 'Ignore Duplicate Keys' to 'Yes'

CREATE UNIQUE NONCLUSTERED INDEX IX_MyIndex ON MyTable
(
   MyColumn
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Answer 2

Hey,

Unique indexes would work. That would only allow a single record. Alternatively, you could setup in code a lock, check for the existence of a record with that ID, if the record exists avoid the insert and notify the users of the issue. But if null, then you go ahead with the insert.

EDIT: A lock could be setup as:

public static object _lock = new object();

public void Save(..)
{
   lock(_lock) {
      //check for existence of an ID
      //process accordingly
   }
}

It's often recommended to do the check both before and after the lock, because as the lock is released, a previous user may have saved the record, but you also have to be concerned with database performance here, so maybe just doing it inside the lock is OK... you also have to be aware that this will hold up other users trying to save the record at the lock point and could affect the performance of your app if the database is slow... something you'll have to be aware of.

HTH.