Re: Need Locking Edification

>> One possibilty regarding the Insert lockouts is that the table is in >> "page" locking mode. Perhaps when the delete locks rows (thus, locking >> keys) that it is deleting, it is also locking the index keys of other >> rows in the same index page, but which are not part of the deletion >> set. Does this make sense??? Absolutely. That is one of the arguments FOR row-level locking. Of course, there are also arguments AGAINST row-level locking which must be understood. Anyway, anytime you delete a row, the whole index page that held the key remains locked until there is either a "commit work" or "rollback work." >> What puzzles me is that we are not deleting by the field that has an >> index (authcode). So what key value exactly is the error message >> referring to that is locked??? No, but you are deleting rows that have a value for authcode (even if that value is 'null'). When a row is deleted, regardless of the method used to select the row, any and all indexes associated with that table must have any references to that row removed. >> Any suggestions/edification as to how to fix and/or work-around >> gratefully appreciated. Thanx. Row-level locking may help in this case. Be aware, however, that the delete transaction will require many more locks. With that in mind, you may have to run several transactions, specifying a single day rather than a range of dates. Below is a reply I sent to another posting about a month ago, covering row- vs. page-level locking. It may help in evaluating your options. Mark Collins mcollins@us.dhl.com =================================================== >> What are the difference between lock mode row and lock mode page? The difference is the number of items which are locked by a single lock. Assume you have a rowsize of 200 bytes, thus you have 9 rows/page on a 2K page, after subtracting timestamp, page header, and slot table entries. With page level locking, when you update a single row, the lock is placed on the page and prevents changes to ANY of the other 8 rows. Page level locking can result in fewer locks being held by an application, at the expense of possibly locking another application/user out of the update they want to perform. Additionally, if the program is changing an adjacent row in the database (i.e., a row on the same page), there is no need to set another lock. SET LOCK MODE TO WAIT 30 will allow a second program to wait up to 30 seconds for the lock to be released by the first program. >> Should I create the tables with lock mode row? Depends. If you have a transaction processing application, with many users accessing the database at the same time, and updates to just one or a few rows at a time, probably the answer is yes. If the application is batch and performs updates to a large percentage of the rows in the database and you don't have to worry about the impact on other users, the answer might be no. The performance of the second type of application could have a small improvement because it would not have to create as many locks. One other criterion to consider is how many rows fit on a page. If there's only one row per page (or each row takes several pages), then use page locking rather than row locking.