SQL Anywhere 9 and locks

Ontsnapt

unread,

Sep 17, 2009, 4:06:24 AM9/17/09

to

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

Reg Domaratzki [Sybase iAnywhere]

unread,

Sep 17, 2009, 9:34:12 AM9/17/09

to

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"

Glenn Paulley [Sybase iAnywhere]

unread,

Sep 17, 2009, 10:18:22 AM9/17/09

to

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

--
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/databasemanagement/sqlanywhere/technicalsupport

ontsnapt

unread,

Sep 18, 2009, 2:30:42 AM9/18/09

to

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

Breck Carter [TeamSybase]

unread,

Sep 18, 2009, 7:17:39 AM9/18/09

to

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,ProcessTime,Port,ReqType,CommLink,NodeAddr,LastIdle,CurrTaskSw,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

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

RisingRoad SQL Anywhere and MobiLink Professional Services
breck....@risingroad.com

ontsnapt

unread,

Sep 18, 2009, 9:04:13 AM9/18/09

to

Thanks

That was the problem.

Eric