Re: Row Level Locking

calvin@florida.nynexst.com (Calvin Chen) writes: >Dear Informixer: > I have a question on informix row level locking. > I have two tables: department and account > table department table account > dept char(3), dept char(3), > amount integer account integer, > amount integer > If I execute the following update statement > UPDATE department > SET amount = > (SELECT SUM(amount) > FROM account > WHERE dept = "DEP") > WHERE dept = "DEP" > Is this considered a one transaction, OR > Is it possible: > After select the sum and before updating department, > other program at the same time update the amount on > account table and update the department table with > the new sum. Then, update of this statement will be > executed which will result in the different amount > on table department and account. Which I want to avoid. If you are executing this statement outside the bounds of a delimited transaction (i.e. using BEGIN WORK and COMMIT WORK), then it is a singleton transaction. An exclusive lock will be placed on the updated row(s) in the department table for the duration of the statement. However, locks will not be placed on any rows in the account table. Thus, another user could conceivably update the amount in a row in the account table that is used in computing the derived sum being stored in the department table. That user, however, could not also update the row in the department table during the instant that the first user had a lock on the row. A method to ensure that no user could update the rows in the account table that are used to compute the sum(amount) is to set the isolation level to Repeatable Read for the duration of this statement. This will cause shared locks to be placed on all the rows in the account table selected to compute sum(amount), thus precluding another user from updating one of those rows. If you are executing this statement from within the bounds of a delimited transaction which also includes the update of one or more rows in the account table that are selected for the computation of sum(amount), then the rows in the account table that are updated will be locked for the duration of the transaction. Your SQL example is the signature candidate for a trigger. I would create triggers that operate on the insert, update, and delete of a row in the account table that execute the very statement in your example. ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.