About autonomous transactions and lock pragma AUTONOMOUS_TRANSACTION & amp; LOCK and transaction transactions

About autonomous transactions and lock pragma AUTONOMOUS_TRANSACTION & LOCK and transaction transactions

The previous problem can be reproduced stably.

Oracle's INV provides an inv_lot_api_pub.auto_gen_lot () interface to automatically generate a lot number for the item. When this interface is called once, the lot number will automatically + 1; check the code in it, first, extract a field start_auto_lot_number from the table MTL_SYSTEM_ITEMS. For example, if the obtained value is 1000, 1000 is regarded as the current lot number, and then a procedure is called, update this field to 1001. the procedure name is update_msi (). finally, verify that the number 1000 already exists. if yes, the system will retrieve 1001 as the current lot number and verify whether it exists until no repeated lot number is found. Then, it will be returned to the place where the api is called.

So what is the problem with this code? Assume that in a scenario where two sessions call this api at the same time, the two sessions may obtain the same number during the data extraction from MSI. well, this is possible. if the two sessions generate a lot number for the same item, but if they are different items, the same lot number will be generated for the two items. the data is wrong.

The solution is to lock the selected lot number through the lock and release the lock after the update is successful.

You can use the for update statement to lock the selected data.

     SELECT auto_lot_alpha_prefix,            start_auto_lot_number       INTO l_lot_prefix,            l_lot_suffix       FROM mtl_system_items      WHERE organization_id = p_org_id        AND inventory_item_id = p_inventory_item_id for update

Once data is selected, the session that uses the same SQL statement cannot obtain the data, which ensures that the same lot number cannot be generated. after successfully updating the lot number, you can use commit to release the lock.

The problem arises. If we use commit to commit this transaction, all the transactions will be committed. If an error is rolled back later, some data will be committed, another part of data is rolled back. the solution is to use autonomous transactions. from the select for update statement to update MSI and commit, we define a procedure and declare that this is a PRAGMA AUTONOMOUS_TRANSACTION. in this way, only the changes made by the autonomous firm will be submitted when the transaction is committed, and the lock will be released for other statements. data Update operations outside of autonomous transactions will not be affected.

By locking and declaring autonomous transactions, we can ensure that the same lot number will not be obtained in the two sessions;