Triggers and locking

Table A contains cash shipment records with a field invoice amount that needs to be updated when the invoice data is loaded. Table B contains the invoice data. Process 1 loads the invoice data, first by inserting invoices into table B, then updating the invoice amount in table a. Process 2 is a critical loading process that also updates 4 other fields in table B. If a locking issue arises with process 1, in which it cannot find the shipment in table A to update (invoice amount), it will skip the shipment. If process 2 cannot find the shipment in table A to update, it will abort and rollback the batch. A re-design of tables and re-coding projects has begun to prevent this. Until this re-design is complete, what would be a recommended locking mode and isolation that can be used with the 2 processes. WOuld it be acceptable to let both processes be set to lock mode wait?

Yes, use lock mode wait -N-; and use optimistic locking protocols in the ap= plications and you shouldn't have any problems. Art=20 -----Original Message----- From: MARGARITA CRUZ <margarita.cruz@dhl.com> Sent: Friday, April 23, 2010 3:09 PM To: ids@iiug.org Subject: Triggers and locking [19834] Table A contains cash shipment records with a field invoice amount that nee= ds=20 to be updated when the invoice data is loaded.=20 Table B contains the invoice data.=20 Process 1 loads the invoice data, first by inserting invoices into table B,= =20 then updating the invoice amount in table a.=20 Process 2 is a critical loading process that also updates 4 other fields in= =20 table B.=20 If a locking issue arises with process 1, in which it cannot find the shipm= ent=20 in table A to update (invoice amount), it will skip the shipment.=20 If process 2 cannot find the shipment in table A to update, it will abort a= nd=20 rollback the batch.=20 A re-design of tables and re-coding projects has begun to prevent this.=20 Until this re-design is complete, what would be a recommended locking mode = and=20 isolation that can be used with the 2 processes.=20 WOuld it be acceptable to let both processes be set to lock mode wait?=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20 [The entire original message is not included]=