thread and dirpath

[LittleSeagull]

Hello Vincent,

I am using libocilib on windows platform with Oracle 19c and I have a problem when I am using thread，pool and dirpath:

There sometimes errors are reported at OCI_DirPathGetMaxRows() function, OCI_PoolGetConnection() function or OCI_DirPathSetBufferSize() function ,and sometimes "OCI-21500" errors are reported.

That I want to know whether multithreading of dirpath's functions is supported.

If it is supported,can you provide a code demo example for reference?
If it is not supported,is there other ways to multithread writing data that can write mode exceeding 10W per second?

Thanks

[vrogier]

Hi,

Can you provide sample code reproducing the issue?
Direct loading operations that load data in the same table/partitions are not meant to be performed from concurrent threads.

Regards,

Vincent

[vrogier]

That might be ways to parallelize to some extend the process but for a given direct path handle, loading data into the database shall be used from a single thread at a time.

[LittleSeagull]

Hi, there's&nbsp;sample code: void worker(OCI_Thread* thread, void* data) { auto* pool = (OCI_Pool *)data; OCI_Connection* pOciCoon = OCI_PoolGetConnection(pool,NULL); int a = OCI_VER_MAJ(OCI_GetOCIRuntimeVersion()); int b = OCI_GetServerMajorVersion(pOciCoon); int batch = 44; uint32_t loadRows = 0; for (int index = 0; index < batch; ++index) { boolean res = TRUE; OCI_DirPath* dp = nullptr; OCI_TypeInfo* tbl = nullptr; uint32_t curBatchRows = 5; tbl = OCI_TypeInfoGet(pOciCoon, "YINWU_MCP_SUPPLY_TEMP", OCI_TIF_TABLE); dp = OCI_DirPathCreate(tbl, /*partition.c_str()*/NULL, 2, curBatchRows); uint32_t bufferSize = curBatchRows * 1000; OCI_DirPathSetBufferSize(dp, bufferSize); OCI_DirPathSetConvertMode(dp, OCI_DCM_DEFAULT); OCI_DirPathSetNoLog(dp, TRUE); OCI_DirPathSetParallel(dp, TRUE); if (OCI_GetOCIRuntimeVersion() &gt;= OCI_9_2) { OCI_DirPathSetCacheSize(dp, curBatchRows * 1000); } //OCI_DirPathSetAtrValue(dp, OCI_ATTR_DIRPATH_SKIPINDEX_METHOD, OCI_DIRPATH_INDEX_MAINT_SKIP_ALL); OCI_DirPathSetConvertMode(dp, OCI_DCM_DEFAULT); /* describe the target table */ OCI_DirPathSetColumn(dp, 1, "TRANSACTION_ID", 22, NULL); OCI_DirPathSetColumn(dp, 2, "PLAN_ID", 22, NULL); /* prepare the load */ OCI_DirPathPrepare(dp); curBatchRows = OCI_DirPathGetMaxRows(dp); OCI_DirPathReset(dp); for (UINT rowIndex = 1; rowIndex <= curBatchRows/* &amp;&amp; iter != pItems-&gt;end()*/; ++rowIndex/*, ++iter*/) { int k = -1; char vals[2][22 + 1] = { 0 }; snprintf(vals[++k], 22, "%d", /*iter.operator-&gt;()-&gt;first*/22); snprintf(vals[++k], 22, "%d", 1); for (int i = 0; i <= k; ++i) { OCI_DirPathSetEntry(dp, rowIndex, i + 1, vals[i], (unsigned int)strlen(vals[i]), TRUE); } } int err_row = OCI_DirPathGetErrorRow(dp); int err_col = OCI_DirPathGetErrorColumn(dp); /* load data to the server */ while (res) { uint32_t state = OCI_DirPathConvert(dp); if (state == OCI_DPR_ERROR) { return; } if (state == OCI_DPR_FULL || state == OCI_DPR_COMPLETE) res = OCI_DirPathLoad(dp); if (state == OCI_DPR_COMPLETE) break; } res = res &amp;&amp; OCI_DirPathFinish(dp); loadRows += OCI_DirPathGetRowCount(dp); OCI_DirPathFree(dp); } OCI_ConnectionFree(pOciCoon); return; } int main(void) { OCI_Thread* th[MAX_THREADS]; OCI_Pool* pool; int i; if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT | OCI_ENV_THREADED)) { return EXIT_FAILURE; } pool = OCI_PoolCreate("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.5)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=aps.yinwu.com)))","aps","aps",OCI_POOL_CONNECTION,OCI_SESSION_DEFAULT,0,MAX_CONN,1); /* create threads */ for (i = 0; i < MAX_THREADS; i++) { th[i] = OCI_ThreadCreate(); OCI_ThreadRun(th[i], worker, pool); } /* wait for threads and cleanup */ for (i = 0; i < MAX_THREADS; i++) { OCI_ThreadJoin(th[i]); OCI_ThreadFree(th[i]); } OCI_PoolFree(pool); OCI_Cleanup(); return EXIT_SUCCESS; }

…

------------------&nbsp;原始邮件&nbsp;------------------ 发件人: "vrogier/ocilib" ***@***.***&gt;; 发送时间:&nbsp;2021年12月8日(星期三) 下午2:57 ***@***.***&gt;; ***@***.******@***.***&gt;; 主题:&nbsp;Re: [vrogier/ocilib] thread and dirpath (Issue #294) That might be ways to parallelize to some extend the process but for a given direct path handle, loading data into the database shall be used from a single thread at a time. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[LittleSeagull]

When using SingleThreadModel to write by dirpath,there's no errros.&nbsp; Instead, an error will be reported. Is there a way around this?

…

------------------&nbsp;原始邮件&nbsp;------------------ 发件人: "vrogier/ocilib" ***@***.***&gt;; 发送时间:&nbsp;2021年12月8日(星期三) 下午2:57 ***@***.***&gt;; ***@***.******@***.***&gt;; 主题:&nbsp;Re: [vrogier/ocilib] thread and dirpath (Issue #294) That might be ways to parallelize to some extend the process but for a given direct path handle, loading data into the database shall be used from a single thread at a time. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[LittleSeagull]

Hi Vincent,
Is there some progress on using multithreading and ocilib's direct path to write to the oracle database?
My project time is dead line and I really need your help. If you have a good solution, please reply to me.

Thanks

[vrogier]

Closing the issue as reported error is an internal Oracle error.