sqlsever高并发下死锁解决

今天业务在高并发下出现了死锁问题,sqlsever
sql重现,开启两个窗口执行.其中一个会在第二次select时报死锁.

Begin Transaction;
SELECT * from t_export_inventory_head 
        WHERE agent_code  = '1122334455' and cop_no = 'Cop20200817001';
Waitfor Delay '00:00:10';
UPDATE t_export_inventory_head SET remove = 1 WHERE id = '796d36e41aa74786bbc6b72289fd9d33';
Waitfor Delay '00:00:10';
SELECT * from t_export_inventory_head 
        WHERE agent_code  = '1122334455' and cop_no = 'Cop20200817001';
Waitfor Delay '00:00:10';
UPDATE t_export_inventory_head SET remove = 0 WHERE id = '796d36e41aa74786bbc6b72289fd9d33';
Waitfor Delay '00:00:10';
COMMIT Transaction;

通过程序中把select语句从事务中分离出去,或者加with(updlock)在select时使用排它锁解决:

Begin Transaction;
SELECT * from t_export_inventory_head  with(updlock)
        WHERE agent_code  = '1122334455' and cop_no = 'Cop20200817001';
Waitfor Delay '00:00:10';
UPDATE t_export_inventory_head SET remove = 1 WHERE id = '796d36e41aa74786bbc6b72289fd9d33';
Waitfor Delay '00:00:10';
SELECT * from t_export_inventory_head with(updlock)
        WHERE agent_code  = '1122334455' and cop_no = 'Cop20200817001';
Waitfor Delay '00:00:10';
UPDATE t_export_inventory_head SET remove = 0 WHERE id = '796d36e41aa74786bbc6b72289fd9d33';
Waitfor Delay '00:00:10';
COMMIT Transaction;

另外这个错误在mysql中不会复现,与事务隔离等级的实现方式有关,MySQL代码如下

START Transaction;
SELECT * from t_export_inventory_head 
        WHERE agent_code  = '3301960C27' and cop_no = 'SPBNKIRG2019122600000014';
select sleep(10);
UPDATE t_export_inventory_head SET remove = 1 WHERE id = '663ee35c856644ac98b47c9d91c57d13';
select sleep(10);
SELECT * from t_export_inventory_head 
        WHERE agent_code  = '3301960C27' and cop_no = 'SPBNKIRG2019122600000014';
select sleep(10);
UPDATE t_export_inventory_head SET remove = 0 WHERE id = '663ee35c856644ac98b47c9d91c57d13';
select sleep(10);
COMMIT;