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);
}
原文：https://www.cnblogs.com/d0minic/p/13572308.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群