may be that my problem has been discussed previously, but I can't find any matching thread. So please be lenient with me.
I'm developing a VB.Net multiuser front end handling data provided by an Informix DS 9.30. Both communicate over the oledb driver which comes along with csdk 2,80.
Now to my problem: I cannot find any substitute for the promotable lock which is easy to set in 4GL. But still I would very much appreciate to set a lock on selected rows which are only possibly going to be updated. With that any other user would be able to still have a glance at the locked data. I tried my luck with dirty read isolation level and lock mode row, but transactions are no substitute for locks since they work only with update, insert or delete statements.
How can I set a shared lock on selected (!) rows outside any transaction frame or is there something wrong with my strategy?
In my eyes the first problem is that any update or delete statement results in a lock on the entire table. Of course the IDS has to rebuild the index and consequently locks it. But in that case I have to minimize the time for any transaction. That's why transaction do not help locking selected rows (which are only promoted to be updated). I'd like to use pessimistic locking.
The second thing is that I do not have an update cursor. So how can I put a shared lock on selected rows but keep the rest of the table unlocked? any ideas?
Originally Posted by gurey
Test if level lock of table is (row or page).
I suggest that alter tables a lock mode (row).