problem with lock of a record

Hi experts, When I want to use a table in which somebody has locked 1 row, the system answers that the record is locked. It seems to me incredible that there is no possibility to delete a record in a table when somebody else locks another record in that table. The system answers: 244: Could not do a physical-order read to fetch next row. 107: ISAM error: record is locked I have already done: alter table w_verklo lock mode (row) and: set lock mode to wait 5 I'm using INFORMIX OWS 7.31.UC6 Thanks for any solution. Danny

Danny De Koster wrote in message <93fgl9$u5l$1@rivage.news.be.easynet.net>... > >When I want to use a table in which somebody has locked 1 row, the system >answers that the record is locked. > >It seems to me incredible that there is no possibility to delete a record in >a table when somebody else locks another record in that table. > >The system answers: 244: Could not do a physical-order read to fetch next >row. > 107: ISAM error: record is locked> >I have already done: alter table w_verklo lock mode (row) > and: set lock mode to wait 5 > Be not incredulous. There are many possibilities. What is happening to you here is an interesting thing... It all comes back to isolation levels, promotable locks and a few other things with big names. By default, a program is in "committed read" isolation level. This means, if a process attempts to read a row that has a write lock, it won't be happy, because the row is not committed. This will affect SELECT, INSERT, DELETE and UPDATE statements which go near the row. So by default, normal reader programs will not suffer from dirty rows. However, there is actually less suffering than imagined with an isolation level of dirty read, so I strongly recommend the use of dirty read mode so that all selects etc may proceed happily on their way. HOWEVER: you are trying to delete a record, and this means that the row and also any index keys must get a lock applied to them until you commit work. You may find it odd that I say the row itself needs locking, but consider: the process of deleting a row takes a bit of time, so once the row is found, it must be locked until all the work of deleting it is completed - housekeeping, finding the index records involved and checking they are not locked etc etc etc. If you are doing an UPDATE operation then of course the row needs locking for the remainder of the transaction. Two questions arise: how does the database engine find the row? how does the database engine implement UPDATES and DELETES? Answer to first question: through the normal channels. Hopefully it can use an index, and if you are deleting one record via a unique key, then it can go DIRECTLY to the record. Even when using an index to find the row, the set of rows indicated by the index is bigger than the set of records implied by the WHERE clause, and so the engine must search sequentially through a small set of rows, using the chosen index to restrict the set to be searched. In the worst case, there is no suitable index and so the engine must search thu the table sequentially, testing the WHERE clause on every row in the table. You may think it should be able to implement this search without regard to locks - after all, if it find a row that satisfies the WHERE, it could then go on to see if there are any locks to pay respects to. BUT, in the time it takes to find a row and then test it's locking, another process may have changed the record to the point that it no longer satisfies the WHERE clause! Earlier versions of the engine used to: search with the WHERE clause, test and get a lock, then test that the row still satisifes the WHERE clause. That was too heavy. The new improved strategy replacing this technique is to use a thing called a promotable lock. These are locks that kinda look like write locks, but they aren't actually official write locks. They just say "I may be promoted to a write lock soon, or I may disappear again". If the lock is promoted to a full write lock, it stays until the end of the transaction, or until the row is deleted. Now, if the engine puts a promotable on the current row it's examining, it can safely test the WHERE clause and then just promote the lock to a write lock if the row needs deleting/updating. Any other process cannot apply a read lock or write lock on a row containing a promotable lock, so it can safely know that the row won't change or disappear while it does other work. Once you understand this ritual, you can begin to understand what's happening in your situation. Consider this: if the engine is trying to pass a promotable lock thru a set of rows (either small or the entire set of rows in the table) then it MAY trip up over a read lock or another write lock. If it trips up there then you get your error numbers, which are classics. This problem also can happen to CURSOR ... FOR UPDATE because they use promotable locks. It can also happen to UPDATE statements for the same reason. Hence, an CURSOR ... FOR UPDATE, update statements, and DELETE statements may all have locking problems if they sweep past a set of rows. You can prevent this in a few ways. Ideally, you only want it to examine exactly 100% and no more of the rows you intend to delete or update. Even one extra row found thru an index but rejected by the rest of the WHERE clause can cause you trouble if it has a lock on that row. 1) If you know that the WHERE clause specifies a unique row, you should have an index on that. 2) If you want to delete a small set of rows which can be totally specified thru a suitable index, with no further parts of a WHERE clause having to be used, consider putting an index on the set of useful fields. This will prevent sweeps through non-matching rows. 3) If you cannot solve the problem with an index, you need to use a SELECT statement to find the rows, then pass the found rows off to a DELETE or UPDATE statement. BEWARE: do not use a CURSOR ... FOR UPDATE because you will have the same problem with locks! It goes like this: define p_rowid integer declare c_finder cursor select rowid from mytable where BIG_UGLY_WHERE_CLAUSE NOTE: you may select the primary key fields instead of the rowid if you are paranoid about using rowids (a portability issue) or if your tables are fragmented without rowids. Contemporary advice is to avoid the rowid, although 5 years ago they were the bees knees for this kind of technique. foreach c_finder into p_rowid delete from mytable where rowid = p_rowid and BIG_UGLY_WHERE_CLAUSE # SEE NOTE BELOW end foreach Using rowid makes the program immune to changes in the PK of a table, but then, it could be asked "what's going on" if you NEED to change a PK, so the real danger there is probably small. NOTE FROM ABOVE: if you are really worried about deleting exactly the correct rows found (and I never have been) then you could insert this in the marked location: # assert correct number of rows deleted case sqlca.sqlerrd[3] # count of rows affected when 1 # celebrate when 0 # handle this case - the row "disappeared"! otherwise call program_panic("this program deleted an unexpected number of rows - has the PK changed?") end case This is called "using assertions" and it's popular in C programming. You've probably seen them 15 times a day if you are running any Microsoft programs, and occasionally they pop up in the message log of the database engine just before it craps out. Assertions are GREAT if some programmer actually takes@@N

Danny De Koster wrote in message <93fgl9$u5l$1@rivage.news.be.easynet.net>... > >When I want to use a table in which somebody has locked 1 row, the system >answers that the record is locked. > >It seems to me incredible that there is no possibility to delete a record >in a table when somebody else locks another record in that table. > >The system answers: 244: Could not do a physical-order read to fetch next >row. > 107: ISAM error: record is locked> >I have already done: alter table w_verklo lock mode (row) > and: set lock mode to wait 5 > Be not incredulous. There are many possibilities. What is happening to you here is an interesting thing... It all comes back to isolation levels, promotable locks and a few other things with big names. By default, a program is in "committed read" isolation level. This means, if a process attempts to read a row that has a write lock, it won't be happy, because the row is not committed. This will affect SELECT, INSERT, DELETE and UPDATE statements which go near the row. So by default, normal reader programs will not suffer from reading the information in dirty rows. However, there is actually less suffering than imagined with an isolation level of dirty read, so I strongly recommend to everyone the use of dirty read mode so that all selects etc may proceed happily on their way. Even with the stronger isolation levels it's easy to paint a picture where inconsistent data is read, so I say, what the hell - dirty read mode is at least honest. HOWEVER: you are trying to delete a record, and this means that the row and also any index keys must get a lock applied to them until you commit work. You may find it odd when I say the row itself needs locking, but consider: the process of deleting a row takes a bit of time, so once the row is found, it must be locked until all the work of deleting it is completed - housekeeping, finding the index records involved and checking they are not locked etc etc etc. If you are doing an UPDATE operation then of course the row needs locking for the remainder of the transaction. Two questions arise: how does the database engine find the row? how does the database engine implement UPDATES and DELETES? Answer to first question: through the normal channels. Hopefully it can use an index, and if you are deleting one record via a unique key, then it can go DIRECTLY to the record. Even when using an index to find the row, the set of rows indicated by the index is bigger than the set of records implied by the WHERE clause, and so the engine must search sequentially through a small set of rows, using the chosen index to restrict the set to be searched. In the worst case, there is no suitable index and so the engine must search thu the table sequentially, testing the WHERE clause on every row in the table. You may think it should be able to implement this search without regard to locks - after all, if it find a row that satisfies the WHERE, it could then go on to see if there are any locks to pay respects to. BUT, in the time it takes to find a row and then test it's locking, another process may have changed the record to the point that it no longer satisfies the WHERE clause! So that leads onto the second question, and it's interesting answer. Earlier versions of the engine used to: search with the WHERE clause, test and get a lock, then test that the row still satisifes the WHERE clause. That was too heavy. When Informix started messing around with this strategy, we started getting odd program failures that taught me the techniques. It helped when the strategy settled down too. The new improved strategy replacing this technique is to use a thing called a promotable lock. These are locks that kinda look like write locks, but they aren't actually official write locks. They just say "I may be promoted to a write lock soon, or I may disappear again". If the lock is promoted to a full write lock, it stays until the end of the transaction, or until the row is deleted. Now, if the engine puts a promotable on the current row it's examining, it can safely test the WHERE clause and then just promote the lock to a write lock if the row needs deleting/updating. Any other process cannot apply a read lock or write lock on a row containing a promotable lock, so it can safely know that the row won't change or disappear while it does other work. Once you understand this ritual, you can begin to understand what's happening in your situation. Consider this: if the engine is trying to pass a promotable lock thru a set of rows (either small or the entire set of rows in the table) then it MAY trip up over a read lock or another write lock. If it trips up there then you get your error numbers, which are classics. This problem also can happen to CURSOR ... FOR UPDATE because they use promotable locks. It can also happen to UPDATE statements for the same reason. Hence, an CURSOR ... FOR UPDATE, update statements, and DELETE statements may all have locking problems if they sweep past a set of rows. You can prevent this in a few ways. Ideally, you only want it to examine exactly 100% and no more of the rows you intend to delete or update. Even one extra row found thru an index but rejected by the rest of the WHERE clause can cause you trouble if it has a lock on that row. 1) If you know that the WHERE clause specifies a unique row, you should have an index on that. 2) If you want to delete a small set of rows which can be totally specified thru a suitable index, with no further parts of a WHERE clause having to be used, consider putting an index on the set of useful fields. This will prevent sweeps through non-matching rows. 3) If you cannot solve the problem with an index, you need to use a SELECT statement to find the rows, then pass the found rows off to a DELETE or UPDATE statement. BEWARE: do not use a CURSOR ... FOR UPDATE because you will have the same problem with locks! It goes like this: define p_rowid integer declare c_finder cursor select rowid from mytable where BIG_UGLY_WHERE_CLAUSE NOTE: you may select the primary key fields instead of the rowid if you are paranoid about using rowids (a portability issue) or if your tables are fragmented without rowids. Contemporary advice is to avoid the rowid, although 5 years ago they were the bees knees for this kind of technique. foreach c_finder into p_rowid delete from mytable where rowid = p_rowid and BIG_UGLY_WHERE_CLAUSE # SEE NOTE BELOW end foreach Using rowid makes the program immune to changes in the PK of a table, but then, it could be asked "what's going on" if you NEED to change a PK, so the real danger there is probably small. NOTE FROM ABOVE: if you are really worried about deleting exactly the correct rows found (and I never have been) then you could insert this in the marked location: # assert correct number of rows deleted case sqlca.sqlerrd[3] # count of rows affected when 1 # celebrate when 0 # handle this case - the row "disappeared"! otherwise

Danny De Koster wrote in message <93fgl9$u5l$1@rivage.news.be.easynet.net>... > >When I want to use a table in which somebody has locked 1 row, the system >answers that the record is locked. > >It seems to me incredible that there is no possibility to delete a record >in a table when somebody else locks another record in that table. > >The system answers: 244: Could not do a physical-order read to fetch next >row. > 107: ISAM error: record is locked> >I have already done: alter table w_verklo lock mode (row) > and: set lock mode to wait 5 > Be not incredulous. There are many possibilities. What is happening to you here is an interesting thing... It all comes down to isolation levels, promotable locks and a few other things with big names. By default, a program is in "committed read" isolation level. This means, if a process attempts to read a row that has a write lock, it won't be happy, because the row is not committed. This will affect SELECT, INSERT, DELETE and UPDATE statements which go near the row. So by default, normal reader programs will not suffer from reading the information in dirty rows. However, there is actually less suffering than imagined with an isolation level of dirty read, so I strongly recommend to everyone the use of dirty read mode so that all selects etc may proceed happily on their way. Even with the stronger isolation levels it's easy to paint a picture where inconsistent data is read, so I say, what the hell - dirty read mode is at least honest. HOWEVER: you are trying to delete a record, and this means that the row and also any index keys must get a lock applied to them until you commit work. You may find it odd when I say the row itself needs locking, but consider: the process of deleting a row takes a bit of time, so once the row is found, it must be locked until all the work of deleting it is completed - housekeeping, finding the index records involved and checking they are not locked etc etc etc. If you are doing an UPDATE operation then of course the row needs locking for the remainder of the transaction. Two questions arise: how does the database engine find the row? how does the database engine implement UPDATES and DELETES? Answer to first question: through the normal channels. Hopefully it can use an index, and if you are deleting one record via a unique key, then it can go DIRECTLY to the record. Even when using an index to find the row, the set of rows indicated by the index is bigger than the set of records implied by the WHERE clause, and so the engine must search sequentially through a small set of rows, using the chosen index to restrict the set to be searched. In the worst case, there is no suitable index and so the engine must search thu the table sequentially, testing the WHERE clause on every row in the table. You may think it should be able to implement this search without regard to locks - after all, if it find a row that satisfies the WHERE, it could then go on to see if there are any locks to pay respects to. BUT, in the time it takes to find a row and then test it's locking, another process may have changed the record to the point that it no longer satisfies the WHERE clause! So that leads onto the second question, and it's interesting answer. Earlier versions of the engine used to: search with the WHERE clause, test and get a lock, then test that the row still satisifes the WHERE clause. That was too heavy. When Informix started messing around with this strategy, we started getting odd program failures that taught me the techniques. It helped when the strategy settled down too... The new improved strategy replacing this technique is to use a thing called a promotable lock. These are locks that kinda look like write locks, but they aren't actually official write locks. They just say "I may be promoted to a write lock soon, or I may disappear again". If the lock is promoted to a full write lock, it stays until the end of the transaction, or until the row is deleted. Now, if the engine puts a promotable on the current row it's examining, it can safely test the WHERE clause and then just promote the lock to a write lock if the row needs deleting/updating. Any other process cannot apply a read lock or write lock on a row containing a promotable lock, so it can safely know that the row won't change or disappear while it does other work. Once you understand this ritual, you can begin to understand what's happening in your situation. Consider this: if the engine is trying to sweep a promotable lock thru a set of rows (either small or the entire set of rows in the table) then it MAY trip up over a read lock or another write lock. If it trips up there then you get your error numbers, which are classics. Unfortunately, the isolation level has got absolutely nothing to do with promotable locks (or write-locks). These locks must happen and nothing can stop them. This problem also can happen to CURSOR ... FOR UPDATE because they use promotable locks. It can also happen to UPDATE statements for the same reason. Hence, a CURSOR ... FOR UPDATE, update statements, and DELETE statements may all have locking problems if they sweep past a set of rows. You can prevent this in a few ways. Ideally, you only want it to examine exactly 100% and no more of the rows you intend to delete or update. Even one extra row found thru an index but rejected by the rest of the WHERE clause can cause you trouble if there is a pre-existing lock on that row. 1) If you know that the WHERE clause specifies a unique row, you should have an index on that. 2) If you want to delete a small set of rows which can be totally specified thru a suitable index, with no further parts of a WHERE clause having to be used, consider putting an index on the set of useful fields. This will prevent sweeps through non-matching rows. 3) If you cannot solve the problem with an index, you need to use a SELECT cursor to find the rows, then pass the found rows off to a DELETE or UPDATE statement. BEWARE: do not use a CURSOR ... FOR UPDATE because you will have the same problem with locks! It goes like this: define p_rowid integer declare c_finder cursor select rowid from mytable where BIG_UGLY_WHERE_CLAUSE NOTE: you may select the primary key fields instead of the rowid if you are paranoid about using rowids (a portability issue) or if your tables are fragmented without rowids. Contemporary advice is to avoid the rowid, although 5 years ago they were the bees knees for this kind of technique. foreach c_finder into p_rowid delete from mytable where rowid = p_rowid and BIG_UGLY_WHERE_CLAUSE # SEE NOTE BELOW end foreach Using rowid makes the program immune to changes in the PK of a table, but then, it could be asked "what's going on" if you NEED to change a PK, so the real danger there is probably small. NOTE FROM ABOVE: if you are really worried about deleting exactly the correct rows found (and I never have been) then you could insert this in the marked location: # assert correct number of rows delete

Danny De Koster wrote in message <93fgl9$u5l$1@rivage.news.be.easynet.net>... > >When I want to use a table in which somebody has locked 1 row, the system >answers that the record is locked. > >It seems to me incredible that there is no possibility to delete a record >in a table when somebody else locks another record in that table. > >The system answers: 244: Could not do a physical-order read to fetch next >row. > 107: ISAM error: record is locked> >I have already done: alter table w_verklo lock mode (row) > and: set lock mode to wait 5 > Be not incredulous. There are many possibilities. What is happening to you here is an interesting thing... It all comes down to isolation levels, promotable locks and a few other things with big names. By default, a program is in "committed read" isolation level. This means, if a process attempts to read a row that has a write lock, it won't be happy, because the row is not committed. This will affect SELECT, INSERT, DELETE and UPDATE statements which go near the row. So by default, normal reader programs will not suffer from reading the information in dirty rows. However, there is actually less suffering than imagined with an isolation level of dirty read, so I strongly recommend to everyone the use of dirty read mode so that all selects etc may proceed happily on their way. Even with the stronger isolation levels it's easy to paint a picture where inconsistent data is read, so I say, what the hell - dirty read mode is at least honest. HOWEVER: you are trying to delete a record, and this means that the row and also any index keys must get a lock applied to them until you commit work. You may find it odd when I say the row itself needs locking, but consider: the process of deleting a row takes a bit of time, so once the row is found, it must be locked until all the work of deleting it is completed - housekeeping, finding the index records involved and checking they are not locked etc etc etc. If you are doing an UPDATE operation then of course the row needs locking for the remainder of the transaction. Two questions arise: how does the database engine find the row(s)? how does the database engine implement UPDATES and DELETES? Answer to first question: through the normal channels. Hopefully it can use an index, and if you are deleting one record via a unique key, then it can go DIRECTLY to the record. Even when using an index to find the row, the set of rows indicated by the index is bigger than the set of records implied by the WHERE clause, and so the engine must search sequentially through a small set of rows, using the chosen index to restrict the set to be searched. In the worst case, there is no suitable index and so the engine must search thu the table sequentially, testing the WHERE clause on every row in the table. You may think it should be able to implement this search without regard to locks - after all, if it find a row that satisfies the WHERE, it could then go on to see if there are any locks to pay respects to. BUT, in the time it takes to find a row and then test it's locking, another process may have changed the record to the point that it no longer satisfies the WHERE clause! So that leads onto the second question, and it's interesting answer. Earlier versions of the engine used to: search with the WHERE clause, test and get a lock, then test if the row still exists and satisifes the WHERE clause. That was too heavy. When Informix started messing around with this strategy, we started getting odd program failures that taught me the techniques. It helped when the strategy settled down too... The new improved strategy replacing this technique is to use a thing called a promotable lock. These are locks that kinda look like write locks, but they aren't actually official write locks. They just say "I may be promoted to a write lock soon, or I may disappear". If the lock is promoted to a full write lock, it stays until the end of the transaction or until the row is deleted. But if the engine puts a promotable on the current row it's examining, it can safely test the WHERE clause and then just promote the lock to a write lock if the row needs deleting/updating. Any other process cannot apply a read lock or write lock on a row containing a promotable lock, so it can safely know that the row won't change or disappear while it does other work. Once you understand this ritual, you can begin to understand what's happening in your situation. Consider this: if the engine is trying to sweep a promotable lock thru a set of rows (either small or the entire set of rows in the table) then it will trip up over a read lock or another write lock. If it trips up there then you get your error numbers, which are classics and old friends. Unfortunately, the isolation level has got absolutely nothing to do with promotable locks (or write-locks). These locks must happen and nothing can stop them. This problem also can happen to CURSOR ... FOR UPDATE because they use promotable locks. It can also happen to UPDATE statements for the same reason. Hence, a CURSOR ... FOR UPDATE, update statements, and DELETE statements may all have locking problems if they sweep past a set of rows. You can prevent this in a few ways. Ideally, you only want the engine to examine exactly 100% and no more of the rows you intend to delete or update. Even one extra row found thru an index but rejected by the rest of the WHERE clause can cause you trouble if there is a pre-existing lock on that row. 1) If you know that the WHERE clause specifies a unique row, you should have an index on that. 2) If you want to delete a small set of rows which can be totally specified thru a suitable index, with no further parts of a WHERE clause having to be used, consider putting an index on the set of useful fields. This will prevent sweeps through non-matching rows. 3) If you cannot solve the problem with an index, you need to use a SELECT cursor to find the rows, then pass the found rows off to a DELETE or UPDATE statement. BEWARE: do not use a CURSOR ... FOR UPDATE because you will have the same problem with locks! It goes like this: define p_rowid integer declare c_finder cursor select rowid from mytable where BIG_UGLY_WHERE_CLAUSE NOTE: you may select the primary key fields instead of the rowid if you are paranoid about using rowids (a portability issue) or if your tables are fragmented without rowids. Contemporary advice is to avoid the rowid, although 5 years ago they were the bees knees for this kind of technique. foreach c_finder into p_rowid delete from mytable where rowid = p_rowid and BIG_UGLY_WHERE_CLAUSE # SEE NOTE BELOW end foreach Using rowid makes the program immune to changes in the PK of a table, but then, it could be asked "what's going on" if you NEED to change a PK, so the real danger there is probably small. NOTE FROM ABOVE: if you are really worried about deleting exactly the correct rows found (and I never have been) then you could insert this in the marked location: # assert correct num

Andrew Hamm wrote in message <3a5bc1c8$1@news.iprimus.com.au>... (4 times) Damn, I hate Outlook express. Sorry about the slightly different repeats. Please read the latest version.