SQL Deadlock - SERIALIZABLE? (TABLOCKX)?

CREATE TABLE [dbo].[MyCompany_memberReq_tbl](
	[SubscriberID] [char](25) NOT NULL,
	[LastName] [char](60) NOT NULL,
	[FirstName] [char](30) NOT NULL,
	[MiddleName] [char](30) NOT NULL,
	[DOB] [datetime] NOT NULL,
	[Gender] [char](1) NOT NULL,
	[RegisterID] [varchar](15) NULL,
	[Createdate] [datetime2](7) NULL,
	[CreateID] [char](15) NULL,
 CONSTRAINT [PK_memreq] PRIMARY KEY NONCLUSTERED 
(
	[SubscriberID] ASC,
	[LastName] ASC,
	[FirstName] ASC,
	[MiddleName] ASC,
	[DOB] ASC,
	[Gender] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [SubscriberID]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [LastName]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [FirstName]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [MiddleName]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT (getdate()) FOR [DOB]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [Gender]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT ('') FOR [RegisterID]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT (getdate()) FOR [Createdate]
GO

ALTER TABLE [dbo].[MyCompany_memberReq_tbl] ADD  DEFAULT (user_name()) FOR [CreateID]
GO

Select allOpen in new window

ALTER PROCEDURE [dbo].[MyCompany_membercheck_SP](  
@subscriberid varchar(25),  
@lastname varchar(60),  
@firstname varchar(35)=NULL,  
@middlename varchar(30)=NULL,  
@dob DATETIME=NULL,  
@gender varchar(1)=NULL,  
@RegisterID varchar(30),  
@secondaryid varchar(15) output  
)  
AS  
BEGIN  
  
 DECLARE @MemberFound as char(1)  
  
 /* Begin look up DB, see if member is found */  
  IF(@gender ='U')  
  BEGIN  
   SET @gender = ' '  
  END  
  
  /* check DB, if member exists */  
  SELECT   
   @secondaryid = m.secondaryid  
  FROM   
   member m (NOLOCK) 
  WHERE  
   m.lastname = @lastname AND   
   m.firstname = @firstname AND   
   m.middlename = @middlename AND   
   m.dob = @dob AND   
   m.sex = @gender AND   
   m.carriermemid = @subscriberid  
  
 /* End look up DB, see if member is found */  
  
 /* if found, update to post member and clear out work table record, if it exists   
  if not found, secondaryID is null, look in work table and pass back Y if not in work table or N if in work table
 */  
 IF (@secondaryid IS NULL) --member not in DB yet  
 BEGIN  
		BEGIN TRANSACTION  
		/* check WT for last name match */  
		IF NOT EXISTS (  
		 select  1 from MyCompany_memberReq_tbl with (TABLOCKX) where lastname = @lastname ) -- match on last name, TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.  
			 BEGIN  
					/* create work record, so other incomings can find and wait*/  
					INSERT INTO MyCompany_memberReq_tbl (lastname,firstname,middlename,dob,gender,subscriberid,RegisterID)  
					VALUES (@lastname,@firstname,@middlename,@dob,@gender,@subscriberid,@RegisterID)  
			      
					/*send back Y to indicate creation for member */  
					SET @MemberFound = 'Y'  
			 END  
		ELSE  
		 BEGIN  
			/* return N if there is no need to create the member, and wait if somone has last name */  
			SET @MemberFound = 'N'   
		 END  
	    
		COMMIT TRANSACTION  
	  
		--set secondaryID to indicate if member should be created or not, depending if another earlier transaction already did it  
		SET @secondaryid = @MemberFound  
	    
		/* update profile table to indicate process, member still not in DB */  
		UPDATE  
		 MyCompany_profile_tbl    
		SET   
		 status ='PREMEMBER'  
		WHERE   
		 RegisterID = @RegisterID  	     
 END  
 ELSE  
	 BEGIN  
			/* member found in DB, clear out work table record if it exist */  
			DELETE FROM MyCompany_memberReq_tbl  
			where lastname = @lastname  
		   
			/* update profile table to indicate process, member now in DB */  
			UPDATE   
			 MyCompany_profile_tbl    
			SET   
			 status ='POSTMEMBER',  
			 secondaryid = @secondaryid  
			WHERE   
			 RegisterID = @RegisterID  
	 END  
  
END  

Select allOpen in new window