Delete command hang

Hi, we performed the follow , and the delete command hang: $ dbaccess mc -; Database selected. > select count(*) from mmo; (count(*)) 1940402 1 row(s) retrieved. > set isolation to dirty read; Isolation level set. > delete from mmo where dest_type = 103; 458: Long transaction aborted. 12204: RSAM error: Long transaction detected. Error in line 1Near character position 36

Not hung. The transaction is/was rolling back that can take longer than the delete took to reach the long transaction notice. Next time you have to delete many rows from a table use my dbdelete utility to avoid a long transaction. It is at least as fast as the delete statement you used in dbaccess and performs the delete in smaller discrete transactions of 8192 rows at a time (by default). Dbdelete is included in my utils2_ak package which you can download from my web site (it's free) at www.askdbmgt.com/my-utilities Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 21, 2016 at 7:52 AM, YONI ROSENFELD <yoni.rosenfeld@atrinet.com> wrote: > Hi, > we performed the follow , and the delete command hang: > $ dbaccess mc -;> > Database selected. > > > select count(*) from mmo;> > (count(*)) > > 1940402 > > 1 row(s) retrieved. > > > set isolation to dirty read;> > Isolation level set. > > > delete from mmo where dest_type = 103;> > 458: Long transaction aborted.> > 12204: RSAM error: Long transaction detected. > Error in line 1> Near character position 36 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1148e49418bbe0053d034ac4

As Art has already said, the transaction was rolling back, which is sometimes very slow, and makes it look like things have hung. You can check to see if a session is rolling back by using "onstat -u", and look for a "R" in the third position of the flags column. You can also use "onstat -x" to monitor a rollback. This shows an estimated rollback time. It's only an estimate, and can be way off, but at least it gives you something to look at. To avoid the rollback you can either make the table RAW so that the deletes are not logged (as long as you are not replicating the database), or find a way to break down the delete into smaller chunks, either by adding different conditions in the WHERE clause or using Art's script. You could also add more logical logs to extend the point at which a long transaction would be triggered. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of YONI ROSENFELD Sent: Wednesday, September 21, 2016 5:53 AM To: ids@iiug.org Subject: Delete command hang [37847] Hi, we performed the follow , and the delete command hang: $ dbaccess mc -; Database selected. > select count(*) from mmo; (count(*)) 1940402 1 row(s) retrieved. > set isolation to dirty read; Isolation level set. > delete from mmo where dest_type = 103; 458: Long transaction aborted. 12204: RSAM error: Long transaction detected. Error in line 1Near character position 36 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.