sybase table Lock with semicolon at end of insert statement

Question

can terminating insert statement with semicolon in Sybase cause Lock on table on which insert takes place? I tried to insert 95 rows in a sybase table with each insert terminated by ; is it possible it could cause huge db lock

Answer 1

No, a semicolon is not going to cause a lock.

The semicolon is merely a command delimiter and has nothing to do with lock management.

You've probably got an open transaction that's holding locks on the newly inserted rows, possibly escalated to a table-level exclusive lock. Are you running in chained transaction mode? Does your client/application have an AUTOCOMMIT setting and if so what is it?

What command/query to run to determine if you're in an open transaction will depend on the actual Sybase RDBMS product you're using (ASE? IQ? SQLAnywhere? Advantage?). [If you have a DBA, s/he should be able to help in determining if you have an open transaction.] [UPDATE: OP has stated this is Sybase ASE in which case the query select @@trancount will display the number of open transactions ... incremented by +1 for each nested begin tran ... will return 0 if there are no open transactions.]

Assuming you're running in chained transaction mode (aka AUTOCOMMIT=false), you could try issuing a commit;; if this closes the transaction then the lock(s) should be released and any blocking should disappear. [One possible issue would be nested open transactions in which case you would need to issue a commit; for each open transaction; in this scenario issuing several commit; commands won't cause any issues while insuring that multiple open transactions are closed.]

Another way to determine if you're in an open transaction ... logout and/or disconnect your client/application from the database; when the database sees your connection disappear it will rollback any open transactions your connection was holding; the rollback would cause the 95 rows to 'disappear' and any blocking locks should also disappear.