topic RE: When commit is executed, it commits all the other users DMLs as well not only the specific login user in Oracle DB. in Product Forum

When commit is executed, it commits all the other users DMLs as well not only the specific login user in Oracle DB.

GouthamMane — Fri, 30 Jul 2021 05:36:00 GMT

HI All,
I am facing some issue on below.

When commit is executed, it commits all the other users DMLs as well not only the specific login user in Oracle DB.

Pl help me on the same.

------------------------------
Goutham Mane Tech Lead
Lead Technical Trainer
TechMahindra
Europe/London
------------------------------

RE: When commit is executed, it commits all the other users DMLs as well not only the specific login user in Oracle DB.

ritansh.jatwani — Sat, 31 Jul 2021 19:14:00 GMT

Hi @Goutham Mane,

Can you please provide more insights on the issue. How are you performing the commit statement?

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: When commit is executed, it commits all the other users DMLs as well not only the specific login user in Oracle DB.

EVIPUTI — Fri, 03 Sep 2021 11:08:00 GMT

If you want to commit a single statement use should use an autonomous transaction . Example :

CREATE OR REPLACE PROCEDURE p_insert
AS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN -- Main transaction suspends here.

INSERT INTO your_table VALUES (1); -- Autonomous transaction begins here.
COMMIT; -- Autonomous transaction ends here.

END; -- Main transaction resumes here.

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification and Optimization Designer(Solutions Architect)
Fidelity International
------------------------------
------------------------------