??Identifying Uncommitted Changes in Oracle: Why it Matters! ??
Ensuring that changes in Oracle databases are committed is crucial to maintaining data integrity and preventing issues like row locks and data corruption. Uncommitted changes often signal coding errors, especially in OLTP applications where data should be inserted, updated, deleted, and committed promptly.
Here are two common coding errors that lead to uncommitted changes:
if ( value_returned_from_select = some_value )
If not handled, pending transactions can be mistakenly committed alongside valid ones, causing data inconsistencies.
Real-World Impact: I once worked with a client facing issues from unhandled NPEs. They found over 30,000,000 (30 million) incomplete order headers due to poor coding practices. Cleaning up required a purge process spanning seven days across 46 tables—a costly process both in time and resources.
It's crucial to manage transactions to prevent lost revenue, deadlocks, and inadvertent data commitments. Regular checks can help. For instance, I use a SQL query to identify open transactions and a cron job to alert and address them promptly.
领英推荐
Final Thoughts:
How do you think the user experience was when they used the website?
How many orders were not taken by the site?
I often wonder how many millions of dollars were lost because of this one error?
Simple put, Leaving transactions opened can cause all kinds of issues from lost revenue, row locks, dead locks or even data being committed to the database that should not be committed.
Proactive monitoring and adherence to good coding practices are key to mitigating these risks. If you encounter open transactions, it's important to report them and collaborate with development to address the root cause.
To identify open transactions in Oracle, I use a specific SQL query, which I execute via a cron job every five minutes. When open transactions are detected, an email alert is sent. I then run an additional SQL statement to capture the open SQL in the session. In many cases, these issues stem from an unhandled null pointer exception. Similar to a rollback, when open transactions occur, it can be challenging for a DBA to pinpoint the cause. I recommend logging a bug with development and allowing them to determine the corrective action.
REM
REM $Author : $Author: rgaydos $
REM Date : $Date: 2022-05-19 01:12:30 -0400 (Thu, 19 May 2022) $
REM Revision : $Revision: 6 $
REM $HeadURL : $HeadURL: https://10.211.55.12/svn/tuningscripts/lib/vuncommited.sql $
REM $Id : $Id: vuncommited.sql 6 2022-05-19 05:12:30Z rgaydos $
REM
set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
column osuser format a10
column process format a10
column machine format a15
column terminal format a10
column program format a25
column module format a10
column logon format a20
select s.sid
,s.serial#
,s.username
,s.machine
,s.lockwait
,t.used_ublk
,t.used_urec
,s.status
,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;
prompt ####################################################
prompt # current transactions:
prompt ####################################################
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.lockwait
,t.used_ublk
,t.used_urec
,s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;