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

sqlserver死锁问题

1.问题

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

 

 

 

查询死锁，杀死死锁进程查询死锁杀死死锁进程

```sql
--查询死锁
select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT' 

--杀死死锁进程
kill 71 

--显示死锁相关信息
exec sp_who2 71
```

```sql
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事务日志

```sql
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.解决死锁

```java
//手动添加事务 
			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);
			}
```