Sqlserver 查询锁占用问题

sqlserver 与mysql、oracle 不同 ，它查询会自动加上锁，当查询与新增、更新操作冲突时会发生死锁的情况，例如下图：

这时候可以加上 WITH(NOLOCK) 申明不占锁

参考语句：

 select  sum(xpsl) xpsl,sum(temp.sl) as xssl,sum(je) as xsje,sum(thje) as thje
        from (
            select count(Distinct  djbh) as xpsl,sum(sl) as sl,sum(je) as je,sum(thje) as thje
            from(
                select  a.djbh,a.sl as sl,0 je,0 thje
                from  ipos_qtlsdmx a  WITH(NOLOCK)  ,ipos_qtlsd  b WITH(NOLOCK) 
                where a.FMID=b.FMID and rq ='2023-02-22' and b.dm1='S001'
                union all
                select a.djbh,0 sl,a.je as je,case when a.sl < 0 then a.je else 0 end as thje
                from  ipos_qtlsdmx  a WITH(NOLOCK) ,ipos_qtlsd  b WITH(NOLOCK) 
                where a.FMID=b.FMID and rq ='2023-02-22' and b.dm1='S001'
           ) vo
        ) temp
   )

但是不占锁时候有个弊端，会造成脏数据或者数据不对应，所以WITH(NOLOCK) 用于只读的报表类的操作，当有先查询然后再更新或者插入这种事务性的操作的时候，就不可以使用了。