sqlserver死锁问题

1.问题

com.microsoft.sqlserver.jdbc.SQLServerException: INSERT 语句与 FOREIGN KEY 约束"FK_ern_ent1"冲突。该冲突发生于数据库"wiz7.x_LN"，表"dbo.Entity", column ‘ent_id‘。

?
SELECT OBJECT_NAME(p.object_id) AS TableName ,
      i.name AS IndexName
FROM sys.partitions AS p
    INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
                                   AND p.index_id = i.index_id

2.查询死锁

--查询死锁
select    
  request_session_id spid,  
  OBJECT_NAME(resource_associated_entity_id) tableName    
from    
  sys.dm_tran_locks  
where    
  resource_type=‘OBJECT‘
?
--杀死死锁进程
kill 354
?
--显示死锁相关信息
exec sp_who2 354

SELECT
  resource_type,
  request_mode,
  resource_description,
  request_session_id,
  request_status,
  resource_associated_entity_id,
  DB_NAME(resource_database_id)as resource_database
FROM
  sys.dm_tran_locks
WHERE
  resource_type <> ‘DATABASE‘
ORDER BY
  request_session_id;

显示sqlserver事务日志

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)
,用户机器名称=SUBSTRING(hostname,1,12)
,是否被锁住=convert(char(3),blocked)
,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型
,last_batch 最后批处理时间,open_tran 未提交事务的数量
from master.sys.sysprocesses
Where status=‘sleeping‘ and waittype=0x0000 and open_tran>0

3.解决死锁

//手动添加事务
DataSourceTransactionManager transactionManager = (DataSourceTransactionManager) ApplicationContext.getBean("transactionManager");
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 事物隔离级别，开启新事务，这样会比较安全些。
TransactionStatus status = transactionManager.getTransaction(def); // 获得事务状态
try {
//逻辑代码，可以写上你的逻辑处理代码
entityMapper.insert(entity);
transactionManager.commit(status);
} catch (Exception e) {
transactionManager.rollback(status);
}

com.microsoft.sqlserver.jdbc.SQLServerException: INSERT 语句与 FOREIGN KEY 约束"xxx"冲突。该冲突发生于数据库"xxx"，表"dbo.Entity", column 'ent_id'。