SqlServer 多并发，锁表功能运用

10月份出差河北

在那边与一个排队叫号系统做接口

接口模式是在本系统整理好信息和生成的排队号一起发送到排队系统指定的表中

接口采用pb写的

主要代码如下（比较多，懒得优化）

 

因为生成排队号的客户端有多个，所以存在并发现象

程序执行一次时必须先从数据库通过select count的形式计算出当前的有多少个

才能再自动加1后往数据库再加入一条数据

如果几个个客户端同时执行，就会同时count出相同的数据

这时同时加1后就会有重号的现象

所以必须对每个客户端select时进行排队操作

即表正在被select或update或insert时不能被其他客户端操作

所以本次采用SqlServer的排他性锁表功能 with TABLOCKX

 

SqlServer还有很多锁表的不同关键词

下一篇再具体介绍

 

choose case ls_yqbz
 case '1'//本院
  if profilestring(Gs_Ini_Filename,'APP','in_cxno','') ='1' then
   insert into lis_to_queue with (TABLOCKX) (date,patient_code,patient_name,isread,isspecial,markinfo)
   values (:ldt_now,:ls_patient_code,:ls_patient_name,'0',:ls_flag,:ls_bz)
   commit using sqlca;
  end if
 case '2'//东区
  if profilestring(Gs_Ini_Filename,'APP','in_cxno','') ='1' then
   insert into lis_to_queue_2 with (TABLOCKX)(date,patient_code,patient_name,isread,isspecial,markinfo)
   values (:ldt_now,:ls_patient_code,:ls_patient_name,'0',:ls_flag,:ls_bz) 
   commit using sqlca;
  end if
end choose

//本院数据插入排队系统
lds_queue = create datastore
lds_queue.dataobject = 'dw_interface_queue'
lds_queue.settransobject(sqlca)
lds_queue.retrieve()
ll_rowcount = lds_queue.retrieve()
if ll_rowcount > 0 then
 for ll_row = 1 to ll_rowcount
  ls_patient_code = lds_queue.getItemString(ll_row,'patient_code')
  ls_patient_name = lds_queue.getItemString(ll_row,'patient_name')
  ls_flag = lds_queue.getItemString(ll_row,'isspecial')
  ll_cnt = lds_queue.getItemNumber(ll_row,'id')
  
  select count(*) into :ll_normal from lis_to_queue with (TABLOCKX) where isspecial = '0'  ;
  select count(*) into :ll_yx     from lis_to_queue with (TABLOCKX) where isspecial = '1'  ;
  select count(*) into :ll_pat    from lis_to_queue with (TABLOCKX) where patient_code = :ls_patient_code  ;
  
  if ll_pat > 0 then ls_patient_code = ls_patient_code + '_' + string(ll_pat)
  
  //不跳号
  if ls_flag = '0' then
   ls_cxno = string(ll_normal + 2000)
  else
   ls_cxno = string(ll_yx + 1000)
  end if
  ls_bz = lds_queue.getItemString(ll_row,'markinfo')
  
  if profilestring(Gs_Ini_Filename,'APP','OUT_DISPLAY','') ='1' THEN
       lb_dp = risdata_to_jlpdxt(ls_patient_code,ls_patient_name,ls_flag,'',ls_cxno,ls_bz,'0001')
       if lb_dp = false then
        Messagebox('提示','该病人信息没有插入到门诊大屏中！！！')
      fun_write_log('.\' + string(today(),'yyyymmdd') + 'fail_insert_log.txt','~r~n' +string(today(),'yyyy-mm-dd') + string(now(),'hh:mm:ss') +'ls_cxno='+ls_cxno)
       end if
    end if
  update lis_to_queue  with (TABLOCKX) set isread = '1' where id = :ll_cnt ;
  update lis_to_queue  with (TABLOCKX) set queue_no = :ls_cxno where id = :ll_cnt ;
 next
end if

if isvalid(lds_queue) then destroy lds_queue

//东院数据插入排队系统
lds_queue_2 = create datastore
lds_queue_2.dataobject = 'dw_interface_queue_2'
lds_queue_2.settransobject(sqlca)
lds_queue_2.retrieve()
ll_rowcount = lds_queue_2.retrieve()
if ll_rowcount > 0 then
 for ll_row = 1 to ll_rowcount
  ls_patient_code = lds_queue_2.getItemString(ll_row,'patient_code')
  ls_patient_name = lds_queue_2.getItemString(ll_row,'patient_name')
  ls_flag = lds_queue_2.getItemString(ll_row,'isspecial')
  ll_cnt = lds_queue_2.getItemNumber(ll_row,'id')
  
  select count(*) into :ll_normal from lis_to_queue_2 with (TABLOCKX) where isspecial = '0' ;
  select count(*) into :ll_yx     from lis_to_queue_2 with (TABLOCKX) where isspecial = '1'  ;
  select count(*) into :ll_pat    from lis_to_queue_2 with (TABLOCKX) where patient_code = :ls_patient_code  ;
  
  if ll_pat > 0 then ls_patient_code = ls_patient_code + '_' + string(ll_pat)
  //不跳号
  if ls_flag = '0' then
   ls_cxno = string(ll_normal + 2000)
  else
   ls_cxno = string(ll_yx + 1000)
  end if
//  messagebox('',string(ll_yx)+ ' '+ string(ll_normal))
  ls_bz = lds_queue_2.getItemString(ll_row,'markinfo')
  if profilestring(Gs_Ini_Filename,'APP','OUT_DISPLAY','') ='1' THEN
//   messagebox('d',ls_patient_code + ls_patient_name + ls_flag + ' ' +ls_cxno)
       lb_dp = risdata_to_jlpdxt(ls_patient_code,ls_patient_name,ls_flag,'',ls_cxno,ls_bz,'0001')
      
   if lb_dp = false then
        Messagebox('提示','该病人信息没有插入到门诊大屏中！！！')
      fun_write_log('.\' + string(today(),'yyyymmdd') + 'fail_insert_log.txt','~r~n' +string(today(),'yyyy-mm-dd') + string(now(),'hh:mm:ss') +'ls_cxno='+ls_cxno)
       end if
   
    end if
  update lis_to_queue_2 with (TABLOCKX) set isread = '1' where id = :ll_cnt  ;
  update lis_to_queue_2 with (TABLOCKX) set queue_no = :ls_cxno where id = :ll_cnt  ;
 next
end if
if isvalid(lds_queue_2) then destroy lds_queue_2
return 1