由浅至深讲述 Sybase 数据库死锁问题+sysprocesses的参数解释

最新推荐文章于 2023-03-07 18:28:30 发布
最新推荐文章于 2023-03-07 18:28:30 发布
阅读量3.3k 收藏 6
点赞数
分类专栏： 数据库 文章标签： sybase 数据库 sql server windows microsoft delete

由浅至深讲述 Sybase 数据库死锁问题

死锁的发生对系统的性能和吞吐量都有重要影响，经检测发现，管理信息系统的死锁主要是因为两个或多个线程（登录）抢占同一表数据资源。引起长时间抢占同一资源不是因为我们需要处理的事务太复杂，时间太长，而往往是因为我们在前端应用程序对数据库作操作时忘了提交。本文介绍一种处理解决这种死锁的方法。

Sybase封锁原理

数据共享与数据一致性是一对不可调和的矛盾，为了达到数据共享与数据一致，必须进行并发控制。并发控制的任务就是为了避免共享冲突而引起的数据不一致。Sybase SQL Server并发控制的方法是加锁机制（LOCKING）。

锁的类型/可申请的锁

已有的锁 S U X

S ∨ ∨ ×

U ∨ × ×

X × × ×

Sybase SQL Server有三种封锁类型：排它锁（exclusive lock,简称X锁）；共享锁（share lock,简称S锁）；更新锁（update lock,简称U锁）。这三种锁的相容矩阵表如下：

×:表示不兼容。∨：表示兼容。Sybase SQL Server是自动决定加锁类型的。一般来说，读（SELECT）操作使用S锁，写（UPDATE,INSERT和delete）操作使用X锁。U锁是建立在页级上的，它在一个更新操作开始时获得，当要修改这些页时，U锁会升级为X锁。

 

查杀锁
-------------------------------------------------
sp_who
sp_lock 156
kill 156

dbcc traceon(3604)是把dbcc的结果输出到屏幕上。
dbcc sqltext(pid)是看指定的sybase进程的操作语句。
pid是用sp_who sp_lock看到的sybase进程。

-------------------------------------------------
LockType列
-------------------------------------------------
sh_intent是意向锁 sh_page共享页面锁

  Sh--共享锁       Ex--独占锁  
  table或intent---锁发生在表  
  page---锁发生在页  
  row----锁发生在行  
  blk----表明这个进程正在阻塞另一个需要获取一个锁的进程，一旦这个进程处理完成，其他进程就可   以继续处理了  
  demand---表明这个进程正在试图获取一个锁

 

锁的力度

SQL Server有两级锁：页锁和表锁。通常页锁比表锁的限制更少（或更小）。页锁对本页的所有行进行锁定，而表锁则锁定整个表。为了减小用户间的数据争用和改进并发性，SQL Server试图尽可能地使用页锁。

当SQL Server决定一个语句将访问整个表或表的大多数页时，它用表锁来提供更有效的锁定。锁定策略直接受查询方案约束，如果update或delete语句没有可用的索引，它就执行表扫描或请求一个表锁定。如果update或delete语句使用了索引，它就通过请求页锁来开始，如果影响到大多数行，它就要请求表锁。一旦一个语句积累的页锁超过锁提升阈值，SQL Server就设法给该对象分配一个表锁。如果成功了，页锁就不再必要了，因此被释放。表锁也在页层提供避免锁冲突的方法。对于有些命令SQL Server自动使用表锁。

锁的状态

SQL SERVER加锁有三种状态：

1）意向锁（intend）—是一种表级锁，它表示在一个数据页上获得一个S或X锁的意向。意向锁可以防止其他事务在该数据页的表上获得排它锁。

2）阻塞（blocking,简记blk）—它表明目前加锁进程的状态，带有blk后缀的锁说明该进程目前正阻塞另一个需要获得锁的进程，只有这一进程完成，其他进程才可以进行。

3）需求锁（demand）—表示此时该进程企图得到一个排它锁。它可以防止在这一表或页上加过多的S锁，她表示某一事务是下一个去锁定该表和该页的事务。

需求锁是一个内部过程，因此用sp_lock是无法看见的。

死锁DEADLOCK

简单地说，有两个用户进程，每个进程都在一个单独的页或表上有一个锁，而且每个进程都想在对方进程的页或表上请求不相容锁时就会发生“死锁”。在这种情况下，第一个进程在等待另一进程释放锁，但另一进程要等到第一个进程的对象释放时才会释放自己的锁。

SQL Server检查是否死锁，并终止事务中CPU时间积累最小的用户（即最后进入的用户）。SQL Server回滚该用户的事务，并用消息号1205通知有此死锁行为的应用程序，然后允许其他用户进程继续进行。

在多用户情形下，每个用户的应用程序都应检查每个修改数据的事务是否有1205号消息，以此确定是否有可能死锁。消息号1025表示该用户的事务因死锁而终止并被回滚。应用程序必须重新开始这个事务处理。

查找死锁原因

既然管理信息系统长时间死锁的原因是由于我们提交或者是提交不当，那么我们就可以通过修改程序防止出现死锁。定位死锁出错处主要经过以下三步：

1）在死锁出现时，用SP_WHO,SP_LOCK获得进程与锁的活动情况。

2）结合库表sysobjects和相应的操作员信息表查出被锁的库表与锁住别人的操作员。

3）根据锁定的库表与操作员的岗位，可以估计出程序大约出错处。询问操作员在死锁时执行的具体操作即可完全定位出错处。最后查找程序并修改之。

用sp_who获取关于被阻碍进程的信息

系统过程sp_who给出系统进程的报告。如果用户的命令正被另一进程保持的锁阻碍，则：

◆status列显示“lock sleep”。

◆blk列显示保持该锁或这些锁的进程标识，即被谁锁定了。

◆loginame列显示登录操作员。结合相应的操作员信息表，便可知道操作员是谁。

 

Fid spid status loginame origname blk dbname cmd
0 1 lock sleep lm lm 18 QJYD SELECT
0 2 sleeping NULL NULL 0 master NETWORK HANDLER
0 3 sleeping NULL NULL 0 master NETWORK HANDLER
……

用sp_lock浏览锁

要得到关于当前SQL Server上保持的锁的报告，可用系统过程sp_lock [spid1[,spid2]]，spid1,spid2是表master.dbo.sysprocesses中的sql server进程id号，用sp_who可以得到锁定与被锁定的spid号：

◆locktype列显示加锁的类型和封锁的粒度，有些锁的后缀还带有blk表明锁的状态。前缀表明锁的类型：Sh—共享锁，Ex—排它锁或更新锁，中间表明锁死在表上（”table”或’intent’）还是在页上（page）. 后缀“blk”表明该进程正在障碍另一个需要请求锁的进程。一旦正在障碍的进程一结束，其他进程就向前移动。“demand”后缀表明当前共享锁一释放， 该进程就申请互斥锁。

◆table_id列显示表的id号，结合sysobjects即可查出被封锁的表名。

执行该进程后屏幕显示

 

Fid Spid locktype table_id page row dbname Class context
0 1 Sh_intent 678293476 0 0 QJYD Non Cursor LockFam dur
0 1 Sh_page 678293476 31764 0 QJYD Non Cursor Lock
0 18 Ex_intent 9767092 0 0 QJYD Non Cursor LockFam dur
……

定位出错处

根据sp_who与sp_lock命令的结果，结合sysobjects和相应的操作员信息表。得到操作员及其在死锁时所操作的库表，便大约可以知道应用程序。（T006）

 

 

 

 

sysprocesses存在于MASTER数据库中
--------------------
sysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。这些进程可以是客户端进程或系统进程。sysprocesses 只存储在 master 数据库中。
列名 数据类型 描述
spid smallint SQL Server 进程 ID。
kpid smallint Microsoft Windows NT 4.0® 线程 ID。
blocked smallint 分块进程的进程 ID (spid)。
waittype binary(2) 保留。
waittime int 当前等待时间（以毫秒为单位）。当进程不处于等待时，为 0。
lastwaittype nchar(32) 表示上次或当前等待类型名称的字符串。
waitresource nchar(32) 锁资源的文本化表示法。
dbid smallint 当前正由进程使用的数据库 ID。
uid smallint 执行命令的用户 ID。
cpu int 进程的累计 CPU 时间。无论 SET STATISTICS TIME ON 选项是 ON 还是 OFF，都为所有进程更新该条目。
physical_io int 进程的累计磁盘读取和写入。
memusage int 当前分配给该进程的过程高速缓存中的页数。一个负数，表示进程正在释放由另一个进程分配的内存。
login_time datetime 客户端进程登录到服务器的时间。对于系统进程，是存储 SQL Server 启动发生的时间。
last_batch datetime 客户端进程上次执行远程存储过程调用或 EXECUTE 语句的时间。对于系统进程，是存储 SQL Server 启动发生的时间。
ecid smallint 用于唯一标识代表单个进程进行操作的子线程的执行上下文 ID。
open_tran smallint 进程的打开事务数。
status nchar(30) 进程 ID 状态（如运行、休眠等）。
sid binary(85) 用户的全局唯一标识符 (GUID)。
hostname nchar(128) 工作站的名称。
program_name nchar(128) 应用程序的名称。
hostprocess nchar(8) 工作站进程 ID 号。
cmd nchar(16) 当前正在执行的命令。
nt_domain nchar(128) 客户端的 Windows NT 4.0 域（如果使用 Windows 身份验证）或信任连接的 Windows NT 4.0 域。
nt_username nchar(128) 进程的 Windows NT 4.0用户名（如果使用 Windows 身份验证）或信任连接的 Windows NT 4.0 用户名。
net_address nchar(12) 指派给每个用户工作站上的网络接口卡唯一标识符。当用户登录时，该标识符插入 net_address 列。
net_library nchar(12) 用于存储客户端网络库的列。每个客户端进程都在网络连接上进入。网络连接有一个与这些进程关联的网络库，该网络库使得这些进程可以建立连接。有关更多信息，请参见客户端和服务器 Net-Library。
loginame nchar(128) 登录名。
thunder09
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
master.sys.sysprocesses相关内容
wmlongshort的博客
12-03 808
sysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。这些进程可以是客户端进程或系统进程。 sysprocesses 只存储在 master 数据库中，是一张视图。 Select * From master.sys.sysprocesses 一、使用sysprocesses查询当前系统被阻塞的会话 　　字段Blocked<...
Sybase数据库死锁问题
03-03
"Sybase数据库死锁问题" 在Sybase数据库中，死锁的问题对系统的性能和吞吐量都有重要影响。经检测发现，管理信息系统的死锁主要是因为两个或多个线程（登录）抢占同一表数据资源。如果不解决死锁问题，将会对系统的...
由浅至深讲述 Sybase 数据库死锁问题
07-15 482
 死锁的发生对系统的性能和吞吐量都有重要影响，经检测发现，管理信息系统的死锁主要是因为两个或多个线程（登录）抢占同一表数据资源。引起长时间抢占同一资源不是因为我们需要处理的事务太复杂，时间太长，而往往是因为我们在前端应用程序对数据库作操作时忘了提交。本文介绍一种处理解决这种死锁的方法。Sybase封锁原理数据共享与数据一致性是一对不可调和的矛盾，为了达到数据共享与数据一致，必须进行并发
通过 sysprocesses 解决Sql死锁问题
xiaouncle的博客
11-07 1851
今天我像往常一样编写新的模块功能（码代码），突然出现了一个奇怪的现象：程序运行之后页面一直处于加载状态。这让我感到不知所措，然后我就按照常用的三大策略：重新生成解决方案、重启VS、重启电脑，结果然并卵……在后面调试过程中我发现Sql语句执行后迟迟拿不到结果，我隐约感觉到问题就出在这里，后来经过查阅资料初步确定数据库被锁住了，那就解决死锁的问题喽！...
SQL server 使用优化脚本
最新发布
D_A_I_H_A_O的博客
03-07 752
SQL server 使用优化脚本
利用sys.sysprocesses检查SqlServer的阻塞和死锁
weixin_33700350的博客
12-20 820
Sys.SysProcesses 系统表是一个很重要的系统视图，主要用来定位与解决Sql Server的阻塞和死锁 视图中主要的字段： 1. Spid：Sql Servr 会话ID 2. Kpid：Windows 线程ID 3. Blocked：正在阻塞求情的会话 ID。如果此列为 Null，则标识请求未被阻塞 4. Waittype：当前连接的等待资源编号，标示是否等待资源，0 或 N...
解决Sybase数据库死锁
11-19
解决Sybase数据库死锁
InnoDB数据库死锁问题处理
12-16
【InnoDB数据库死锁问题处理】 InnoDB存储引擎在MySQL中是默认的事务型存储引擎，它支持行级锁定机制，这使得多个事务能够并行处理数据而不相互干扰。然而，这种灵活性也可能导致死锁（Deadlock）问题，即两个或多...
Mybatis update数据库死锁之获取数据库连接池等待
09-01
总的来说，理解数据库事务和锁机制对于排查和解决Mybatis中的死锁问题至关重要，而优化数据库连接池配置和事务管理则能有效防止连接池等待的情况。在开发过程中，保持良好的编程习惯，及时提交或回滚事务，可以避免...
sql最全的常用命令语句
02-23
sql最全的常用命令语句 询某个数据库的连接数 select count(*) from Master.dbo.SysProcesses where dbid=db_id() --前10名其他等待类型 SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%' OR wait_type like 'LAZYWRITER_SLEEP%' --CPU的压力 SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 --表现最差的前10名使用查询 SELECT TOP 10 ProcedureName = t.text, ExecutionCount = s.execution_count, AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ), AvgWorkerTime = s.total_worker_time / s.execution_count, TotalWorkerTime = s.total_worker_time, MaxLogicalReads = s.max_logical_reads, MaxPhysicalReads = s.max_physical_reads, MaxLogicalWrites = s.max_logical_writes, CreationDateTime = s.creation_time, CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 ) FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_physical_reads DESC SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 , SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间, SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％], SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％] FROM sys.dm_os_wait_stats --一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。 --查看进程所执行的SQL语句 if (select COUNT(*) from master.dbo.sysprocesses) > 500 begin select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a end select text,a.* from master.sys.sysprocesses a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) where a.spid = '51' dbcc inputbuffer(53) with tb as ( select blocking_session_id, session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) ), tb1 as ( select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)', total_scheduled_time,reads,writes,logical_reads from tb a inner join master.sys.dm_exec_sessions b on a.session_id=b.session_id ) select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id --当前进程数 select * from master.dbo.sysprocesses order by cpu desc --查看当前活动的进程数 sp_who active --查询是否由于连接没有释放引起CPU过高 select * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time < dateadd(minute, -10, getdate()) --强行释放空连接 select 'kill ' + rtrim(spid) from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -60, getdate()) and login_time < dateadd(minute, -60, getdate()) --查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU) select spid,cmd,cpu,physical_io,memusage, (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text from master..sysprocesses order by cpu desc,physical_io desc --查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局 SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY usecounts,p.size_in_bytes desc SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt WHERE plan_generation_num >1 ORDER BY qs.plan_generation_num SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time, COUNT(*) AS number_of_statements FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt GROUP BY qt.text ORDER BY total_cpu_time DESC --统计总的CPU时间 --ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间 -- 计算可运行状态下的工作进程数量 SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id FROM sys.dm_os_workers AS o INNER JOIN sys.dm_os_schedulers AS s ON o.scheduler_address=s.scheduler_address AND s.scheduler_id<255 WHERE o.state='RUNNABLE' GROUP BY s.scheduler_id
数据库中某个视图锁死现象（SQL Server : error 1222）
专注于大数据领域相关技术推广、学习、交流和合作。
11-13 1220
根据实际项目实践情况，分享一种数据库中视图锁死现象的问题发现、分析、验证和解决的方法。
在查询分析器中删除正在运行中的数据库
.NET爱好者
06-19 991
只要有一个进程访问这个数据库，就不能删除数据库。你可以连接到系统数据库master。如：use mastergo--用代码先删除连接此数据库的进程declare @d varchar(8000)set @d=select @d=@d+ kill +cast(spid as varchar)+char(13)from master..sysprocesses where
SQLServer-TEMPDB性能问题排查
weixin_34220623的博客
06-02 807
SQLServer-TEMPDB性能问题排查 问题描述 实例卡慢-金融云用户-RT敏感 问题分析 现场 首先看下进程状态： select * from sys.sysprocesses where spid>50 and lastwaittype<>'MISCELLANEOUS' and status<>'sleepi...
sys.sysprocesses视图的使用小结
weixin_30279671的博客
12-23 646
Sys.SysProcesses 系统表是一个很重要的系统视图，主要用来定位与解决Sql Server的阻塞和死锁包含正在 SQL Server 实例上运行的进程的相关信息。这些进程可以是客户端进程或系统进程。 视图中主要的字段： 1. Spid：Sql Servr 会话ID 2. Kpid：Windows 线程ID 3. Blocked：正在阻塞求情的会话 ID。如果此列为 Null，则标识请求...
无法用排他锁锁定该数据库，以执行该操作。 (Microsoft SQL Server，错误: 5030)
彼岸舞的博客
05-11 686
SqlServer重命名错误 原因是因为其他的线程占用这SqlServer的连接 可通过SQL查看 select spid from master.dbo.sysprocesses where dbid=db_id('databaseName') 采用SQL杀死占用方 kill spid 记得切换为别的别的数据库,要不然本身会占用一个连接,还杀不掉 再次重...
SQL Server定时自动抓取耗时SQL并归档数据脚本分享
weixin_34221773的博客
06-19 171
原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享SQL Server定时自动抓取耗时SQL并归档数据脚本分享 第一步建库 USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] GO 第二步创建sp_who3存储过程 -- http://sqlserverplanet.com/dba/a-b...
整理 SQLSERVER排查阻塞
purpleTassels的博客
01-03 1417
转载 查看原文 SQL Server允许并发操作，BLOCKING是指在某一操作没有完成之前，其他操作必须等待，以便于保证数据的完整性。 BLOCKING的解决方法要查看BLOCKING的头是什么，为什么BLOCKING头上的语句执行的很慢。通常来讲只要我们能找到BLOCKING头上的语句，我们总能够想出各种各种的办法，来提升性能，缓解或解决BLOCKING的问题。 但是问题的关键是
mysql2008优化_SQL server 2008 数据库优化常用脚本
weixin_29858113的博客
02-01 99
--查询某个数据库的连接数select count(*) from Master.dbo.SysProcesses where dbid=db_id()--前10名其他等待类型SELECT TOP 10 * from sys.dm_os_wait_statsORDER BY wait_time_ms DESCSELECT *FROM sys.dm_os_wait_stats WHERE wait_...
通讯SYBASE数据库维护参考办法
06-14
通讯SYBASE数据库维护参考办法

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值