I have a simple table that is pretty much static in our application in that the application does not directly write or update any records in it. I want to modify the size of a field to this table but I get an error stating "User DBA has the row in table billing locked". When I look at the locks in the DB, there is a lock showing on this table. The lock is a Shared Schema lock with a Lock duration of Transaction. My question is - why would this lock appear on this table if I never issue and UPDATE or INSERT? I do have some code that will query this table but I close the cursor. I don't see why I would need to issue a COMMIT on a read cursor. Is there anyway around this or am I doing something wrong. Using SQL ANYWHERE 12 (18.104.22.16873). Any help is greatly appreciated !!!!
asked 18 Mar '15, 09:21
Operations like ALTER TABLE need absolute, complete, exclusive access to the table. Operations like SELECT obtain a schema lock on the table that prevent other connections from gaining exclusive locks... in this case, to prevent the other connection from messing up the schema while this connection is working with the table... hence the name "schema lock".
This is standard behavior for SQL Anywhere, and you solve it by issuing a COMMIT on the connection that did the SELECT... or in the case of Sybase Central, which is the biggest offender for holding schema locks on everything in sight, you just shut it down while doing your ALTER.
Another approach (sometimes used in production environments) is to let the ALTER TABLE sit there blocked until all other connections let go (perhaps overnight). In this case the ALTER TABLE connection has to set the blocking option to ON. That's the default: sit and wait, rather than raise the exception you saw, which leads me to believe you saw that message in Sybase Central which (I think) sets blocking to OFF so it will immediately choke when faced with a lock that blocks it from proceeding. In a production environment, you typically want blocking set on (the default) so that application connections will wait momentarily while other connections quickly update and commit stuff, rather than have connections failing all over the place when users collide.