SYBASE

Mid:1,containerid:35customerid :10041,taskid : 65,subtaskid : 433 Exception : StatementCallback; uncategorized SQLException for SQL [update center..JP10_eticket_record1222 set transmit_flag=’c’ where transmit_flag=’b’]; SQL state [ZZZZZ]; error code [1204]; ASE has run out of LOCKS. Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure ASE with more LOCKS.
; nested exception is java.sql.SQLException: ASE has run out of LOCKS.
Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure ASE with more LOCKS.

案例一：扩展lock数量解决
Sybase ASE12.5.3ESD#7的错误日志文件中频繁报下面的错误信息：
Error 1204, Severity 17, State 2 occurred for User ‘sa’. Client IP address is ‘XXX.XXX.XXX.XXX’.
SQL Text: select xxxx from xxxxx where xxxxx
查询Sybase错误信息手册，Error 1204是由锁数量不够导致的。
Error 1204的错误信息内容应该为：
SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure SQL Server with more LOCKS.

自ASE12.5.2后，数据库引擎向错误日志文件中记录Error1204时还记录一些额外的信息，比如：客户端IP地址，登录用户名以及执行的SQL语句。
我这次看到的错误日志文件中仅有一些额外信息，比如：for User ‘sa’. Client IP address is ‘XXX.XXX.XXX.XXX’. SQL Text: sql_clause，并没有显示SQL Server has run out of LOCKS.这一行信息。
验证一下是否锁数量不够导致的Error 1204？
1> sp_monitorconfig “number of locks”
2> go
Usage information at date and time: Oct 20 2011 3:31PM.

Name Num_free Num_active Pct_act Max_Used
Num_Reuse

     -----------

number of locks 298531 1469 0.49 ** 301452**
0
(return status = 0)

曾经用过的最大锁数量301452已经超过配置的number of locks参数值300000了。
*解决方法：
*
不管显示不显示run out of LOCKS信息，已经确定是锁数量不够。适当增大number of locks试试解决此错误。
sp_configure “number of locks”,400000
go

将锁的数量加到40万。
*PS：
*
关于Error 1204显示的错误信息内容，Targeted CR List for ASE 12.5.4 ESD #7中有一个bug id ：468870 可能与之有关。
When printed in the error log, the message “Error 1204, Severity 17,State 1 occurred for User ‘ < user > ‘. Client IP address is ‘ < address > ‘“ uses an inconsistent format making it difficult to search for within the error log.
这可能是我没在ASE12.5.3的错误日志文件中看到SQL Server has run out of LOCKS.类似信息的原因。

案例二：杀掉锁相关进程，释放锁的方式解决
use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql=’declare getspid cursor for
select spid from sysprocesses where dbid=db_id(‘‘‘+@dbname+’’’)’
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec(‘kill ‘+rtrim(@spid))
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end

–用法
use master
exec killspid ‘数据库名’

—然后再：
CREATE CLUSTERED INDEX biblio ON biblio ( recid, marcnum )