In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.
The following configuration works for me on Oracle 11.2.0.2:
C prog:
[oracle@node1 ~]$ cat exec_hmmftg.c
#include <stdio.h>
int exec_hmmftg ( char *p1 , char *p2, char *p3 )
{
return 5;
}
[oracle@node1 ~]$
Compile it, and make sure it's placed in $ORACLE_HOME/bin or $ORACLE_HOME/lib (configuration can be changed to allow other directories) - compiler flags will differ for AIX too.
gcc -shared -o $ORACLE_HOME/lib/exec_hmmftg.so exec_hmmftg.c
Configure EXTPROC in the listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.6)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PHIL112)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
(SID_NAME = PHIL112)
)
)
Restart the listener with lsnrctl stop followed by lsnrctl start.
Create the reference to the external library & create the function to interface between PL/SQL and the C prog:
[oracle@node1 ~]$ sqlplus phil/phil
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 17:51:21 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE LIBRARY CPROGLIB UNTRUSTED AS '/u01/app/oracle/product/11.2.0/dbhome_1/lib/exec_hmmftg.so ';
2 /
Library created.
SQL> CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1 IN VARCHAR2, P2 IN VARCHAR2, P3 IN VARCHAR2)
2 RETURN BINARY_INTEGER
3 IS EXTERNAL NAME "exec_hmmftg"
4 LIBRARY CPROGLIB
5 LANGUAGE C
6 PARAMETERS(P1 STRING, P2 STRING, P3 STRING, RETURN INT);
7 /
Function created.
SQL>
Test the function:
SQL> select call_c_program('a','b','c') from dual;
CALL_C_PROGRAM('A','B','C')
---------------------------
5
SQL>
Not quite sure what you're doing wrong without seeing the full C you've written. Try and setup this simple case I've made for you, then add functionality bit by bit from there.
Best Answer
Transaction A sees the updated definition of function
myfunc()immediately. (But see the effect of cache below.)It will fail. (But see the effect of cache below.)
Postgres DDL commands are fully transactional. While transaction B does not commit, both transactions would continue to see different versions of the function. But concurrent transactions do see committed changes in system catalogs. Would seem obvious in default isolation level
READ COMMITTED. But you cannot even prevent this with isolation levelsREPEATABLE READorSERIALIZABLE.If you should have called the function in transaction A before transaction B committed a change, the local cache can interfere. In my tests, one more call worked with the cached (old) function before the next call was aware of the change and answered accordingly.
I did not find documentation how the system catalog cache behaves for this exactly (still might exist somewhere). I am not convinced the last bit (one more call answered from cache) is the best possible behavior.
BTW, your steps 3. - 5. can be reduced to just 4., without any difference. Explicit or implicit transaction wrappers work the same:
3. Start a transaction from client B4. In transaction B, use "create or replace function" to revise the definition of myfunc()
5. Commit transaction B