0

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

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
Prateek Gupta
  • 135
  • 1
  • 2
  • 11

1 Answers1

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.

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • thb ank you for this detailed ans,my mistake is clear to me.I was supposed to run update on DB consisting of 3DML's 1delete-->95 inserts-->1 update auto commit was off I ran delete first and then proceeded to inserts,where I was told huge db locks have occured in DB after I started running my statements – Prateek Gupta Jan 21 '21 at 15:22
  • 1
    good to hear you've found the problem; thanks for the update; fwiw .... if you need to perform all 3x DML's as a single unit of work (ie, all within a single transaction) and you're causing excessive blocking, you may want to work with the DBA and application teams to determine a good time to perform the operation; if you're affecting a large number of rows the DBA should also be able to help with ensuring you don't fill up the transaction log – markp-fuso Jan 21 '21 at 15:32
  • 1
    Performing all 3 DML';s in a single batch should have been fine in my case, thanks,your answer made it very clear for me on where I went wrong.much appreciated and learned a valuable lesson. – Prateek Gupta Jan 21 '21 at 15:38