We use SQL Anywhere 12. We have a table defined as below called reservation. I added a column promotion_rn using the alter table statement below and this took an hour on my local development machine but when I ran the add column sql in production the statement took 5+ hours. Could anyone offer any advice on why this may have happened and possibly what I can do to improve this time when adding columns to this table in future? I ran the sql statement in iSQL.
The reservation table contains approximately 750,000 rows.
The result of "call sa_table_fragmentation( 'reservation' )" gives the following:
asked 27 Oct '14, 23:39
The Smart Money (i.e., Volker) says the ALTER TABLE is blocked by a schema lock.
The next time you do this, start a second ISQL session and run SELECT * FROM sa_conn_info() to see if the BlockedOn column is non-zero for the first ISQL session.
The following example shows that connection 2,'database-maintenance' is blocked by connection 3.
SELECT * FROM sa_conn_info(); Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection 4,'Foxhound-p001','DBA',0,'2014-10-28 07:38:44.294','COMMIT','local','',0,0,0,0,,'',0, 3,'adhoc-queries','k.delacruz',0,'2014-10-28 07:38:50.160','FETCH','local','',0,0,0,0,,'',0, 2,'database-maintenance','DBA',0,'2014-10-28 07:36:54.572','EXEC','local','',0,0,3,0,,'DBA.inventory',0,
To see what locks are being held by connection 3, run SELECT * FROM sa_locks ( 3 ) which in the case of an ALTER TABLE that is being blocked by a SELECT with no COMMIT will show a 'Schema' lock:
SELECT * FROM sa_locks ( 3 ); conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier 'adhoc-queries',3,'k.delacruz','BASE','DBA','inventory',,'Schema','Transaction','Shared',
In cases like this, you can wait until everyone else is off the database, or you can ask them politely to get off the database (or stop working after doing a commit), or you can kick them off by calling p_drop_other_connections().
Unlike row locks, AFAIK there is no mechanism that can exactly pinpoint "this block is caused by that schema lock"... in this case sa_conn_info() and sa_locks() work because connection 3 is only holding one lock so that must be the one. In the real world, however, evil-doer connections hold millions of locks so it can be a challenge to diagnose blocks caused by non-row locks.
All rows need to be copied when a new column is added so for a huge table with a lot of rows it will need some time to do this copy, in my experience this happens even if the column is a nullable one.
answered 28 Oct '14, 08:50