Re: Record Locking

dberg@informix.com (David I. Berg) writes: >bathini@netcom.com (Suresh Kumar Bathini) writes: >>Hi There: >>I have two clients in their transactions as below. >>Client1----> >> begin work >> fetch a record R1 >> >> ..... >> update R1 >> commit work >>Client2-----> >> begin work >> fetch a record R1 >> update R1 >> commit Work >>Both the clients fetched the same record but client2 updated R1 before >>client1 did, so this resulted in client1's R1 to be an old copy. Is >>there a way I could lock a record to read in a transaction? Or is this >>kind of problem accepatable in Relational world as R1 is a copy and >>doesn't guarentee its state in d/b? >You need to declare your cursor to select R1 "FOR UPDATE". Then: UPDATE R1 >WHERE CURRENT OF cursorname. Now, when Client1 fetches R1, an Update Lock >will be placed on the record. When Client2 attempts to fetch the same >recor with an Update Lock, it will be unable to acquire it. If LOCK MODE >is not set to WAIT, then the engine will immediately return -246 to >Client2. I'll make things a little messier here. How do I achieve serializability if the fetch uses a join from two tables? In that case I cannot use FOR UPDATE to lock the records. Beirne -- ------------------------------------------------------------------------------- Beirne Konarski | Subscribe to the unicycling mailing list. beirnek@summitis.com | Send request to: "Untouched by scandal" | unicycling-request@mcs.kent.edu