有关死锁的问题

lw1a2 2006-06-27 09:33:15

客户最近老反应系统很慢。我让客户在系统慢的时候，执行了以下SQL：

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid)
union
select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

...全文

709 27 打赏收藏转发到动态举报

写回复

用AI写文章

27 条回复

切换为时间正序

请发表友善的回复…

发表回复

lw1a2 2006-07-06

打赏
举报

详细流程见
http://blog.csdn.net/lw1a2/archive/2006/07/05/878813.aspx

lw1a2 2006-07-03

打赏
举报

谢谢老大的。虽然大多看不懂-.-

lzhs 2006-07-01

打赏
举报

原来sp_cursor系统还有这么多！
受教了。

LouisXIV 2006-07-01

打赏
举报

收下了:)

OracleRoob 2006-07-01

打赏
举报

up!!

lw1a2 2006-06-30

打赏
举报

又出现死锁了，通过mac找到ip，然后找到了出问题的那台机器。发现死锁的原因了，希望只有这一处

十一月猪 2006-06-30

打赏
举报

刀我来接你分了
^_^

zjcxc 2006-06-30

打赏
举报

这就是与楼主提到的sp_cursorclose相关的几个存储过程.

zjcxc 2006-06-30

打赏
举报

sp_cursor
Can be used to request inserts and positioned updates or deletes on API server cursors.

Syntax
sp_cursor [@cursor =] cursor_handle,
[@optype =] optype,
[@rownum =] rownum,
[@table =] 'table'
{, [@param1 =] value1 [,...n] }

Arguments
[@cursor =] cursor_handle
Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
[@optype =] optype
Is a the operation to perform. optype is int, with no default and can be one of these values.

Value Description
1 Update row (?).
4 Insert row.
33 Update row.
34 Delete row.
40 提取当前页的行

[@rownum =] rownum
Is the number of the row to update in the fetch cache. rownum is int, with no default.
[@table =] 'table'
Is the name of the table to update (an empty character string seems to be ok). table is sysname, with no default.
Return Code Values
0 (success) or 1 (failure).

Result Sets
None.

Permissions
Execute permissions default to the public role.

Example
USE pubs

-- Create a dynamc cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193

-- Fetch the next 2 lines; this puts lines 1 and 2 in the fetch buffer

EXEC sp_cursorfetch @cursor, 2, 0, 2

-- Update the second line in the fetch buffer

EXEC sp_cursor @cursor, 33, 2, '', @intCol=5, @charCol='x'

-- Close the cursor

EXEC sp_cursorclose @cursor

zjcxc 2006-06-30

打赏
举报

sp_cursoroption
Sets various options for API server cursors.

Syntax
sp_cursorclose [@cursor =] cursor_handle,
[@code =] code,
{ [@value =] value
| [@cursorname =] cursorname }

Arguments
[@cursor =] cursor_handle
Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
[@code =] code
Is the option code. code is int, with no default and can be one of these values.

Value Description
1 ???
2 Set cursor name.

[@value =] value
Is the value of the selected option. value is int, with no default.
[@cursorname =] cursorname
Is the name of the cursor. cursorname is sysname, with no default.
Return Code Values
0 (success) or 1 (failure).

Result Sets
None.

Permissions
Execute permissions default to the public role.

Example
USE pubs

-- Create a dynamc cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193

-- Name the cursor

EXEC sp_cursoroption @cursor, 2, 'myCursor'

-- Use a cursor variable to access the cursor

DECLARE @x CURSOR

EXEC sp_describe_cursor @x out, N'global', 'myCursor'

FETCH NEXT FROM @x

-- Use the cursor directly by name

FETCH NEXT FROM myCursor

-- Close the cursor

EXEC sp_cursorclose @cursor

zjcxc 2006-06-30

打赏
举报

sp_cursorfetch
Fetches a row or block of rows from an API server cursor.

Syntax
sp_cursorfetch [@cursor =] cursor_handle
[, [@fetchtype =] fetchtype]
[, [@rownum =] rownum OUTPUT]
[, [@nrows =] nrows OUTPUT]

Arguments
[@cursor =] cursor_handle
Is the cursor handle. cursor_handle is int, with no default.
[@fetchtype =] fetchtype
Is the fetch type. fetchtype is int, with a default of 2 and can have one of these values.

Value Description
0x0001 First row.
0x0002 Next row.
0x0004 Previous row.
0x0008 Last row.
0x0010 Absolute row index.
0x0020 Relative row index.
0x0040 By value (???).
0x0080 Refresh.
0x0100 Result set info.
0x0200 Previous noadjust (?).
0x0400 Skip update concurrency (???).

[@rownum =] rownum OUTPUT
Is the row number. rownum is int, with a default of NULL.
[@nrows =] nrows OUTPUT
Is the number of rows to fetch. nrows is int, with a default of NULL (fetch all rows).
Return Code Values
0 (success) or 1 (failure).

Result Sets
Returns the requested row or group of rows from the cursor.

Remarks
In addition to fetching rows, the 'result set info' fetch type can be used to retrieve information about the cursor (current row in @rownum and total number of rows in @nrows).

Permissions
Execute permissions default to the public role.

Example
USE pubs

-- Create a dynamc read-only cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193

-- Fetch the next 3 lines

EXEC sp_cursorfetch @cursor, 2, 0, 3

-- Close the cursor

EXEC sp_cursorclose @cursor

sp_cursorclose
Closes ande deallocates an API server cursor.

Syntax
sp_cursorclose [@cursor =] cursor_handle

Arguments
[@cursor =] cursor_handle
Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
Return Code Values
0 (success) or 1 (failure).

Result Sets
None.

Permissions
Execute permissions default to the public role.

Example
USE pubs

-- Create a dynamic read-only cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193

-- Close the cursor

EXEC sp_cursorclose @cursor

zjcxc 2006-06-30

打赏
举报

sp_cursoropen
Defines the attributes of an API server cursor, such as its scrolling behavior and the statement used to build the result set on which the cursor operates, then populates the cursor. The statement can contain embedded parameters.

Syntax
sp_cursoropen [@cursor =] cursor_handle OUTPUT,
[@stmt =] 'stmt'
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]

[
{, [@paramdef =] N'parameter_name data_type [,...n]' }
{, [@param1 =] value1 [,...n] }

]

Arguments
[@cursor =] cursor_handle OUTPUT
Is the name of a declared integer variable to receive the cursor handle. cursor_handle is int, with no default.
[@stmt =] 'stmt'
Is a string containing a single SELECT statement or a single stored procedure call. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:

'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list and the parameter values list.

[@scrollopt =] scroll_options OUTPUT
Is the cursor scroll type. scroll_options is int with a default of 1 (keyset-driven), and can be a combination of these values (exactly one of the first 5 must be specified).

Value Description
0x0001 Keyset-driven cursor.
0x0002 Dynamic cursor.
0x0004 Forward-only cursor.
0x0008 Static cursor.
0x0010 Fast forward-only cursor.
0x1000 Parameterized query.
0x2000 Auto fetch.
0x4000 Auto close.
0x8000 Check acceptable types.
0x10000 Keyset-driven acceptable.
0x20000 Dynamic acceptable.
0x40000 Forward-only acceptable.
0x80000 Static acceptable.
0x100000 Fast forward-only acceptable.

On return, @scrollopt contains the type of cursor actually created, which may not match what was requested.
[@ccopt =] concurrency_options OUTPUT
Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic) and can be a combination of these values (exactly one of the first 4 must be specified).

Value Description
0x0001 Read-only.
0x0002 Scroll locks.
0x0004 Optimistic.
0x0008 Optimistic w/ checksum values (?).
0x2000 Open on any SQL.
0x4000 Update keyset in place.
0x10000 Read-only acceptable.
0x20000 Locks acceptable.
0x40000 Optimistic acceptable.

On return, @ccopt contains the type of cursor actually created, which may not match what was requested.
[@rowcount =] rowcount OUTPUT
Is the name of a declared integer variable to receive the number of affected rows. rowcount is int with no default value.
[@paramdef =] N'parameter_name data_type [,...n]'
Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef. If the Transact-SQL statement in stmt does not contain parameters, @paramdef is not needed. The default value for this parameter is NULL.
[@param1 =] value1
Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the Transact-SQL statement in stmt has no parameters.
n
Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure).

Result Sets
Returns the result set generated by stmt, but containing no rows.

Remarks
sp_cursoropen is a more powerful (and programmatic) way of creating server-side cursors on SQL Server.

Permissions
Execute permissions default to the public role.

Examples
A. Create a cursor for a simple SELECT statement
This simple example creates a dynamic read-only cursor for a SELECT statement with no parameters.

USE pubs

-- Create a dynamc read-only cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable', 2, 8193

-- Close the cursor

EXEC sp_cursorclose @cursor

B. Create a cursor for a parameterized SELECT statement
This example creates a dynamic read-only cursor for a SELECT statement with 2 parameters.

USE pubs

-- Create a dynamc read-only cursor

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable WHERE col1=@P1 AND col2 LIKE @P2', 2, 8193, N'@P1 INT, @P2 VARCHAR(255)', 10, '%x%'

-- Close the cursor

EXEC sp_cursorclose @cursor

C. Create a cursor for a stored procedure call
This example creates a dynamic read-only cursor for a stored procedure with 2 parameters (the procedure must return only one result set or the cursor creation will fail). Note that output parameters can also be used and return values retrieved via output parameters.

USE pubs

-- Create a dynamc read-only cursor

DECLARE @cursor INT

DECLARE @retval INT

EXEC sp_cursoropen @cursor OUTPUT, 'EXEC @P1=myProc @P2', 2, 8193, N'@P1 INT OUTPUT, @P2 INT', @retval, 1

-- Close the cursor

EXEC sp_cursorclose @cursor

zjcxc 2006-06-30