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); } ```
不积跬步,无以至千里;不积小流,无以成江海。