<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures" />
<link rel="next" href="PERFTIPS.html" title="Performance Tips" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="ASYNCEXECMULTITHREAD" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures">Prev</a> | <a class="link" href="PERFTIPS.html" title="Performance Tips">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div class="selected">
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
<div>
<a href="#synchronization" title="Synchronization">Synchronization</a>
</div>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="ASYNCEXECMULTITHREAD" />
<h2>9.12. Asynchronous Execution and Multithreading in Virtuoso/PL</h2>
<p>
Many application tasks benefit from parallel execution. This is specially true of I/O intensive workloads
where each thread spends a large amount of time waiting for the network or disks. Typical tasks include
crawling the web and importing large data sets. The whole process must not stop just because there is a
file cache miss or because there is round trip latency or a name resolution delay on the net.
</p>
<p>
To this effect, Virtuoso/PL provides the async_queue object. A stored procedure may create an async_queue
that will be served by a pool of worker threads. The size of this pool can be set when creating the queue.
</p>
<p>
The thread which made the queue can use the queue to pass procedure/parameter list pairs to the threads.
If a thread is available, the thread will execute the request, if not, the next thread of the pool to
become free will take the oldest queued item and execute it. Thus the queue is served in a FIFO fashion multiplexed over n threads.
</p>
<p>
The owner of the queue can check on the results of execution either collectively or individually. Each worker thread
has its own transaction and worker threads may end up waiting for each other own database locks and can deadlock.
The worker thread code is responsible for committing its own transaction and handling any deadlock retries or such.
</p>
<p>
When the thread owning the queue makes a request, a request number is returned. This number can be used to later
request the return value and error status of the request. A queue cannot be persisted in a database table and cannot
be passed between threads. A queue can be passed between procedures and kept in data structures such as arrays.
Queue are internally reference counted and when the last reference drops the queue is freed. If a queue is freed
while there is still activity on behalf of the queue, the activities that are ongoing are finished, all requests
that are not started are discarded and all values and error states are discarded. The queue and associated
resources are thereafter freed.
</p>
<p>
There is a pool of 20 threads that are shared among all async_queues on the system. Thus, the count of threads given for
the queue is a maximum and does not guarantee that this quantity of threads be used in reality. If no thread is available
in the pool, i.e. other async queues have exhausted the entire pool, the thread making the request ends up executing the
item synchronously. One should take this possibility into account when deciding transaction boundaries, otherwise this is
transparent and the calling thread still gets a request handle and can later check for its completion.
</p>
<p>
Queues take procedures and argument lists instead of texts of SQL statements in order to save the time of compiling the text.
It is desirable for best performance to supply the name of the procedure in its full form, with full qualification and matching case.
</p>
<p>
Consider the following code samples:
</p>
<div>
<pre class="programlisting">
create table aqi (n int);
-- The worker procedure. Insert one row and commit.
create procedure INS1 (in n int)
{
--dbg_obj_print ('ins1 ', n);
insert into AQI (N) values (n);
commit work;
return '22';
}
create procedure taq1 (in x int, in thrs int := 1)
{
declare aq, res, err any;
declare n int;
aq := async_queue (thrs);
for (n:= 0; n < x; n:=n+1)
{
res := aq_request (aq, 'DB.DBA.INS1', vector (n));
}
return (aq_wait (aq, res, 1, err));
}
-- This procedure makes a queue with a given number of worker threads, then makes a set of requests and waits for the result
of the last one. Note that this is not necessarily the last to complete if there are multiple threads serving the queue.
</pre>
</div>
<a name="synchronization" />
<h3>9.12.1. Synchronization</h3>
<p>
It is possible to add requests to a queue at all times. It is also possible to check for the result of any request made so far,
by either blocking to wait for it or just checking its status.
</p>
<div>
<pre class="programlisting">
aq_wait (in aq any, in req_no int, in block int, out err any) returns any
</pre>
</div>
<p>
The aq_wait function takes the queue, a request number returned by aq_request, a blocking flag and an output parameter for
the error state.
</p>
<p>
If there was no error, the error state is set to 0. If the procedure was undefined, the error state is set to 2.
If there was a SQL state signalled from the procedure called on the worker thread, the error state is set to an array
of three elements: The integer 3, the SQL state string and the text of the message. If aq_wait is terminated by an external event,
then an error indicating this is signalled and the state waited for is lost. This can only happen when all transactions are
killed by shutdown or going to a single user state.
</p>
<p>
If the blocking flag was zero and the request was not complete, then the error output parameter is set to 1 and 1 is returned.
</p>
<p>
Once aq_wait has retrieved a state, the state is no longer retained in the queue.
</p>
<p>
The aq_wait_all function allows waiting for all activity to complete but discards individual return states. If some of running activities is terminated by sql error, this error will be raised in the thread executing aq_wait_all function call.
</p>
<p>
Note that it is possible to get a deadlock between the requesting thread and a worker thread and that this deadlock
cannot be detected by the database engine since this does not involve a cycle in database locks themselves. Thus,
aq_wait signals an error if the thread calling it holds database locks. Manually committing or rolling back before
calling aq_wait is necessary if the thread can belong to a transaction that holds locks.
</p>
<p>
Thus, it is most practical to explicitly commit all work on the requesting thread before calling aq_wait or aq_wait_all.
</p>
<div>
<pre class="programlisting">
create procedure taq_all (in x int, in thrs int := 1)
{
declare aq, res, err any;
declare n int;
aq := async_queue (thrs);
for (n:= 0; n < x; n:=n+1)
{
res := aq_request (aq, 'DB.DBA.INS1', vector (n));
}
aq_wait_all (aq);
}
-- This procedure is guaranteed to wait for all requests to be completed but will discard individual error states.
</pre>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures">Previous</a>
<br />CREATE PROCEDURE Syntax - External hosted procedures</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="PERFTIPS.html" title="Performance Tips">Next</a>
<br />Performance Tips</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
21
