Today they asked me to solve a problem that occured when creating a
foreign key. This problem occured on some databases. The message I get
is SQL state 40001 [Sybase][ODBC driver][Adaptive Sever Anywhere]User '
DBA' has the row in 'INSTALLATIE' locked.

The database on my machine is started with dbeng9.exe and I am sure our
application does not lock the row. So, I suppose out client starts the
database with dbsrv9 and its not shutdown in the normal way. If I
restart the database locks seems to be there and cleared after a certain
time.

Is this correct?
If so, can I run a script to remove locks from non-existing connections?

Thanks
Eric

Ontsnapt wrote:
Try calling sa_conn_info() and sa_locks() to get information about
active connections to your database and the locks that they are holding.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Please reply only to the newsgroup

Documentation : Exercise your WRITE @DocCommentXchange: DCX.sybase.com
SQL Anywhere Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere
-> Optionally set filter to "Display ALL platforms IN ALL MONTHS"

Locks are held only by connections. Disconnecting releases all locks
held by that connection. So does COMMIT and ROLLBACK. So there is no
need to "remove locks from non-existing connections".

My guess is that your ALTER TABLE is tripping over a schema lock, rather
than a row lock. Possibly because of an open DBISQL session.

Glenn




Ontsnapt wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

I start the database with dbisql. This should be the only active connection.

sa_locks();

I see 32 locks most lock_type 'S'.
connection is for all locks 1000000001
user_id is for all locks DBA

DBA.ANIMAL_COUNTS SPT NULL
DBA.ANIMAL_COUNTS E 4294968617
DBA.ANIMAL_COUNTS SPA000 1095216661806
DBA.INSTALLATIE S (NULL)
DBA.INSTALLATIE S 545


sa_conn_info();

Number 1000000001 1
Name ev_startup_animal_counting_385 <empty>
UserID DBA DBA
DBNumber 0 0
LastRegTime <empty> 2009-09-18 08:23:09.747
RegType unknown(0) CURSOR_OPEN
CommLink NA local
NodeAddr NA <empty>
LastIdle 0 61



DBA.INSTALLATIE is the table thats causing the problem. If I wait a few
seconds an run the command again the locks are gone.


Thanks
Eric


Reg Domaratzki [Sybase iAnywhere] wrote:

It looks like there is an EVENT connection running
ev_startup_animal_counting_385

The sa_conn_info() result set includes a BlockedOn column that will
tell you if one connection is blocked by another:

SELECT * FROM sa_conn_info();

Number,Name,Userid,DBNumber,LastReqTime,ProcessTim e,Port,ReqType,CommLink,NodeAddr,LastIdle,CurrTask Sw,BlockedOn,LockName,UncmtOps
2,'ddd-2','DBA',0,'2009-09-18
07:17:07.358',,,'STMT_EXECUTE','local','',1727,,1, 519,2
1,'ddd-1','DBA',0,'2009-09-18
07:17:10.046',,,'CURSOR_FETCH','local','',486,,0,0 ,1

Breck





On 17 Sep 2009 23:30:42 -0700, ontsnapt <ontsnapt (AT) hotmail (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Thanks

That was the problem.

Eric

Breck Carter [TeamSybase] wrote: