Best Practise on Concurrency Issues in .Net Application

You can use the optimistic concurrency option of typed datasets in .NET which will throw an exception if any of the data has been modified since you performed a Select on the table which stops concurrent modifications.

I have also done what you have with manually locking a record by a bit field but I did it a little differently. In a Delphi application the connection remained open so I created a table called "Locks" with the structure:

/****** Object:  Table [dbo].[Lock]    Script Date: 09/22/2009 07:33:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Lock](
	[RefNum] [int] IDENTITY(1000,1) NOT NULL,
	[CreateDate] [datetime] NULL,
	[UserName] [varchar](40) NOT NULL,
	[Location] [int] NULL,
	[Spid] [int] NULL,
	[LoginTime] [datetime] NULL,
	[TableName] [varchar](30) NULL,
	[RecordNum] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[RefNum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Lock] ADD  DEFAULT (getdate()) FOR [CreateDate]
GO

I would insert a locking entry for the table with the row identifier and lock it based on SPID, the SQL Connection ID. Whenever a lock was requested I would purge locks for all the stale SPIDS which solves your problem about network errors. When a network connection drops the SQL Connection will timeout and the SPID will become invalid, thus the next request to lock a record would purge the SPID and the record would become available. In the application layer I would request locks with a sproc.
The sprocs match locks based on SPID and login time because the SPIDs are re-used and it is safe to assume that another user will get an existing spid that may have a lock, so using two fields solves this problem.

function TFunctionFm.CreateLock(aTable: String;
  aRecord: Integer): Boolean;
begin
  ADOStoredProcLock.Parameters[1].Value := Sec.User;
  ADOStoredProcLock.Parameters[2].Value := Sec.Location;
  ADOStoredProcLock.Parameters[3].Value := aTable;
  ADOStoredProcLock.Parameters[4].Value := aRecord;
  ADOStoredProcLock.ExecProc;
  Result := ADOStoredProcLock.Parameters[5].Value > 0;
end;

The underlying sproc:

ALTER Procedure [dbo].[LockRow] (
  @UserName varchar(40), @Location int, @TableName varchar(30), @RecordNum int, @RetValue int OUTPUT
) 
As
Begin
Declare @LoginTime DateTime
Select @LoginTime = login_time
From master.dbo.sysprocesses
Where spid = @@Spid
--Delete stale SPIDS
Delete From Lock
Where
Not Exists (
  Select *
  From master.dbo.sysprocesses as sys
  Where sys.Spid = Lock.Spid and Sys.Login_Time = Lock.LoginTime
)
--Allow Spid to lock Table/Ref
Insert Into Lock (UserName, Location, Spid, LoginTime, TableName, RecordNum)
Select @UserName, @Location, @@Spid, @LoginTime, @TableName, @RecordNum
Where @RecordNum Not In (Select RecordNum From Lock Where TableName = @TableName)
/*** RETURN INSERT COUNT [0/1] ***/
Set @RetValue = @@ROWCOUNT
End

A return value of 1 means the lock was successful, 0 indicates it was not.

--

Now the reason I said all of that is because you can still apply this concept in .NET with disconnected datasets, although it is not best practices and I personally do not recommend doing so. What you could do to accomplish this is create a static SqlConnection and hold it open for the lifetime of the application and use that spid to lock against.

Or you can use optimistic concurrency of the dataset.

Hi,

I've found an even better solution then yours, one that does not need any stored procedures to clean up.

We are now playing with NHibernate and manual locking of the record.
By creating a global temp table called TableName.PrimaryKeyValue.
After the save-operation, the table is dropped,if you close the connection, the table is dropped, if the application halts unexpectetly SQL Server will clean up after me.
We are integrating this manual locking into the objects itself and in the object repository.

I've got this solution from a guy on experts-exchange ...
And I think we will be going this way,

Regards,
Sven Peeters

I'm glad to see you found a solution to your problem. Please mark this thread as solved if you have found an answer to your question and good luck!

Also post the link to the experts-exchange page that contained the logic you are referring to if possible.