Re: Weird issues with id locks and auto-generated ids

On Tue, 25 May 2004 15:00:07 -0400, CKGeier wrote: > This is a multipart message in MIME format. --=_alternative PLEASE, do not post MIME to this list. It doubles the bandwidth needed to store/display the message and many denizens of the list do not use MIME enabled readers. > One of our users terminals got stuck in a loop today. She was trying to > update an id record, and it kept saying the record was locked. I ran > 'wholock' and saw that her process was adding thousands of exclusive locks Sounds like the app was looping and trying inserts over and over when it could not get a lock on some other resource. This would be consistent with the other symptoms you are seeing. Sounds like a poorly written application that's trying recovery manually instead of relying on transactions. See my comments below. > on an index to the id record. I told her to press escape and that seemed to > release them. I then looked to see if someone else had that record locked, > but they didn't. So I told her to try again, and it started to add the > exclusive index locks again, this time I couldn't stop it without trying to > close her database connections (that didn't work) and finally I had to kill > her processes. Well, as you probably know, that left thousands of locks open > on the system. No, when the session was killed the engine detects the defunct client and kills the session (which you could do manually with onmode -z <sessid> BTW) and releases any locks the session held. The only problem would be if the client were a Windoze app, as windoze does not always close the network connection properly when a task ends, so the engine may not know that the client was gone (and on some old SCO and old SunOS clients it can take up to 5 minutes to reset a dead connection). > Well, also, at the same time, there was a long transaction and we finally > had to shutdown. After bringing it back up, we had a problem that has > occurred before. Before letting others onto the system, we added a test > record to the id record and it added the auto-generated id number of 658342. > However, the maximum before we had the trouble was set to 555872, and there > were no records in between the two numbers, nor should there be. So this > loop the user was in appeared to update the maximum auto-generated number in > this id table. This happens because IDS increments the serial value for a table when a serial number is requested NOT when the transaction is committed. This means that if 1000 records are inserted and the transaction is rolled back the next record to be inserted will skip 1000 serial values. That's just the way it works. It may be that the application was looping on an attempted insert and when another operation failed, due to the lock, it rolled back the transaction. This would increment the serial value. Is it possible the the app handles lock-outs be sleeping and trying again in a loop instead of using the SET LOCK MODE TO WAIT <nsec> feature of the engine? That would explain things. > How could this happen? This is entirely weird! Anyone else seen this? Art S. Kagel