怎样解决用户并发数据,该怎么处理

怎样解决用户并发数据
有什么办法处理这个存储过程并发吗！
要1个1个的来！！
比如30个人同时执行这个某存储过程
等这个存储过程处理完毕，
下1个人再次接着执行




下面是个同时提交考题的1个存储过程！！！！


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<feifan>
-- Create date: <20008-10-15>
-- Description:<学生答题入库-写表：ot_paper,ot_TestResult>
-- =============================================
ALTER PROCEDURE [dbo].[st_TestTotalInfo_add] 
@paperid INT ,
  @studentid VARCHAR(11) ,
  @serial VARCHAR(20) ,
  @ip VARCHAR(20) ,
  @ret INT OUTPUT
AS 
BEGIN
  SET NOCOUNT ON ;
  SET @ret = 1
  --检测TEMP表的中的题目数和试卷的试题数是不是相等，不相等就退出
  DECLARE @qnum INT ,
  @Answernum INT
  SELECT @qnum = COUNT(id)
  FROM ot_question
  WHERE paperid = @paperid AND stauts = 0 --试卷的试题数
  SELECT @answernum = COUNT(1)
  FROM AnswerTemp.dbo.ot_AnswerRecordsTemp
  WHERE studentid = @studentid AND paperid = @paperid AND serial = @serial
  IF @qnum <> @answernum 
  BEGIN 
  SET @ret = 4 --试题数不相等
  RETURN
  END

  -- begin tran--开始事务

--插入答题记录表*******************
  INSERT INTO ot_AnswerRecords_new ( questionid,qoption,qscore,paperid,studentid,addtime,serial )
  SELECT questionid,qoption,qscore,paperid,studentid,addtime,serial
  FROM AnswerTemp.dbo.ot_AnswerRecordsTemp
  WHERE studentid = @studentid AND paperid = @paperid AND serial = @serial 
   
--记录试题分数和答题人数


  UPDATE ot_paperquestion
  SET test_num = a.test_num + 1,total_score = a.total_score + b.qscore
  FROM ot_paperquestion a ,
  dbo.ot_AnswerRecords_new b
  WHERE b.studentid = @studentid AND b.paperid = @paperid AND serial = @serial AND a.paper_id = b.paperid AND a.question_id = b.questionid

  DECLARE @myscore FLOAT
  SELECT @myscore = SUM(qscore)
  FROM AnswerTemp.dbo.ot_AnswerRecordsTemp
  WHERE studentid = @studentid AND paperid = @paperid AND serial = @serial 
  IF @myscore > 100 
  SET @myscore = 100 
  --更新试卷答题次数和部分
  UPDATE dbo.ot_paper
  SET test_num = test_num + 1,total_score = total_score + @myscore
  WHERE id = @paperid
 
--删除临时记录表
  DELETE FROM AnswerTemp.dbo.ot_AnswerRecordsTemp
  WHERE studentid = @studentid AND paperid = @paperid AND serial = @serial
   
--更新分数
  UPDATE dbo.ot_TestResult
  SET end_date = GETDATE(),test_score = @myscore
  WHERE studentid = @studentid AND paperid = @paperid AND serial = @serial 
   
   
  DECLARE @reward INT --为积分赋值
  SET @reward = CASE WHEN @myscore > 0 AND @myscore < 60 THEN 50
  WHEN @myscore >= 60 AND @myscore < 70 THEN 70
  WHEN @myscore >= 70 AND @myscore < 80 THEN 100
  WHEN @myscore >= 80 AND @myscore < 90 THEN 150
  WHEN @myscore >= 90 AND @myscore < 95 THEN 200
  WHEN @myscore >= 95 AND @myscore < 100 THEN 260
  WHEN @myscore = 100 THEN 300
  END

  --更新用户帐户表的经验值字段
IF NOT EXISTS ( SELECT 1
  FROM ot_cpstar
  WHERE studentid = @studentid AND paperid = @paperid ) 

  BEGIN
  UPDATE ot_account
  SET degree = degree + @reward
  WHERE studentid = @studentid
--插入ot_rewardDetail表
  INSERT INTO ot_rewardDetail ( studentid,reward,rewardtype,ip,keyid,serial )
  VALUES ( @studentid,@reward,1,@IP,@paperid,@serial )
  SET @ret = 0 ---成功
  END 
   
  SET @ret = 0  
  IF @@error <> 0 
  BEGIN
  SET @ret = 2 ---出错
  END
  ELSE 
  BEGIN
  SET @ret = 0
  END


  --commit tran
   
END




[解决办法]
同时执行一个存储过程没问题,关键只要操作的数据,不是同一条记录就行了.
[解决办法]
这个估计得把隔离级别调整到可串行化
[解决办法]
楼主可以用applock的方式,

SQL code

-- 申请排他锁exec sp_getapplock @Resource='st_TestTotalInfo_add', @LockMode = 'Exclusive' ,@LockOwner = 'Session'-- 获取排他锁后执行存储过程    exec st_TestTotalInfo_add ... -- 释放排他锁exec sys.sp_releaseapplock @Resource='st_TestTotalInfo_add',@LockOwner = 'Session', @DbPrincipal = 'public'--> 当2个连线同时执行以上代码时, 会出现等待.