jkrueger@dgis.dtic.dla.mil (Jon) (02/01/90)
loreen@otl.SanDiego.NCR.COM (Loreen Prinz) writes: >Does Ingres support asynchronous SQL???? I give up. What is asynchronous SQL? SQL that returns without waiting for the query to execute? -- Jon -- Jonathan Krueger jkrueger@dtic.dla.mil uunet!dgis!jkrueger The Philip Morris Companies, Inc: without question the strongest and best argument for an anti-flag-waving amendment.
perk@infmx.UUCP (Paul Perkovic) (03/02/90)
In article <745@dgis.dtic.dla.mil> jkrueger@dgis.dtic.dla.mil (Jon) writes: >loreen@otl.SanDiego.NCR.COM (Loreen Prinz) writes: > >>Does Ingres support asynchronous SQL???? > >I give up. What is asynchronous SQL? >SQL that returns without waiting for the query to execute? Exactly. In the current ANSI working draft for SQL3, any executable SQL statement can be prefixed by an ASYNC clause, which supplies a statement identifier and indicates that the DBMS may execute the statement asynchronously. For example: ASYNC 5 UPDATE EMP SET SALARY = SALARY * 1.06 WHERE STATUS = 'A'; might give each employee with an acceptable status a 6% raise. The user or application can then do other things while the database engine performs the update. Later, the application can use the <test completion statement> to determine whether the update has completed, e.g.: TEST (5) COMPLETION; which returns an exception (SQL statement not yet complete) if asynchronous statement 5 is still executing, and returns normally if it is finished. An alternative form allows your application to wait until the asynchronous statement completes: WAIT (5) COMPLETION; A COMMIT WORK waits for all outstanding asynchronous statements to complete; a ROLLBACK WORK terminates any outstanding asynchronous statements. Thus, the scope of potential overlap among asynchronous executions is limited to a single transaction. If this facility remains in the document by the time SQL3 is standardized (mid 1990's), it will permit a standard-conforming application program to take advantage of available parallelism in the environment, such as might be available in a distributed network of database servers. Otherwise, each SQL statement blocks until it completes, and overlapped processing is not possible within the single application context. Sorry, I cannot answer Loreen's original question; perhaps someone from Ingres can? Oh, in case you're wondering . . . is <test completion statement> an <SQL executable statement>? Yes, of course. Does that mean you can write: ASYNC 6 WAIT (6) COMPLETION; which obviously means to wait for itself to complete? Why not? / perk perk@informix.com ...{pyramid|uunet}!infmx!perk Paul Perkovic (415) 591-7700 anytime Informix Software, Inc. (415) 926-6821 17 Rinconada Cir./ Belmont, CA 94002 4100 Bohannon Dr./ Menlo Park, CA 94025
jkrueger@dgis.dtic.dla.mil (Jon) (03/04/90)
perk@infmx.UUCP (Paul Perkovic) writes: >If this facility remains in the document by the time SQL3 is standardized >(mid 1990's), it will permit a standard-conforming application program to >take advantage of available parallelism in the environment Yes, like Toonses the cat who could drive a car -- just not very well! Explicitly parallel programming languages (and function call extensions to existing languages) have not done well in terms of programmer productivity, application safety. It's unlikely that database programming will do any better. For instance: ASYNC 5 UPDATE EMP SET SALARY = SALARY * 2 WHERE STATUS = 'A'; ASYNC 6 SELECT * FROM EMP WHERE SALARY > 20000; No, locking doesn't save you, it's undefined which query acquires the locks first. The TEST and WAIT constructs tell when a query has finished, not when it has started. Consider that existing RDBMSs already let multiple queries execute in parallel. They use an abstraction called the process. Each process sends a single stream of queries. Each query blocks until it completes. No query blocks queries in other processes. If all you want is nonblocking submission of queries and event-driven collection of results, nothing stops you now from creating as many processes as you have queries. No changes to query language syntax are required. If it seems hairy, consider that it's exactly as (un)safe as asynchronous SQL. The issues are identical. Look: pid1 := create_process (my_favorite_database_manager, my_database) write (pid1, "UPDATE EMP SET SALARY = SALARY * 2 WHERE STATUS = 'A'"; pid2 := create_process (my_favorite_database_manager, my_database) write(pid2, "SELECT * FROM EMP WHERE EMP.FINGERS > 10") while (active_process_count > 0) { whatpid, whatevent := get_next_event() if (whatevent == PROCESS_LOST) close_down(whatpid) else if (whatevent == NETWORK_DOWN) break else deal_with_it(whatpid, whatevent) } } close_down(pid1) close_down(pid2) From there, you can abstract away process creation, and allow default DBMS and database, with a set of jacket routines, allowing a perhaps more pleasant syntax: qid1 := send_query("UPDATE EMP SET SALARY = SALARY * 2 WHERE STATUS = 'A'"; qid2 := send_query("SELECT * FROM EMP WHERE EMP.FINGERS != 10") while (active_query_count > 0) { whatqid, whatevent := get_next_event() if (whatevent == UNDIFFERENTIATED_QUERY_FAILURE) close_down(whatpid) else if (whatevent == GLOBAL_QUERY_PROBLEMS) break else deal_with_it(whatqid, whatevent) } } cleanup(qid1) cleanup(qid2) Either way, your application can now submit nonblocking queries without changing the query language at all. Or as you put it, overlapped processing is possible within the single application context. Of course, all the disadvantages of explicit parallism in user programs are still there. The key, of course is that mysterious deal_with_it() function has to know a lot about dependencies among queries: deal_with_it(qid, event) { if (event == COMPLETION) submit(waiting_for(qid)) } We're just chasing it into the woodwork (indeed, an infinite regress characteristizes a lot of reasoning about database problems -:) How does the mysterious waiting_for work? Don't tell me it consults a database! You're regressing again!! Similarly it's no solution to re-express the problem in various ways, inviting though it is. The fundamental problem isn't the right way to express parallism, it's the right way to detect and enforce sequentialism. It's very questionable that user programs are the best place to do this. A better place might be the query optimizer. Consider again the streams of queries coming in from processes. The query optimizer sees them all (yes, even in multiserver, although of course the implementation issues are hardly irrelevant, but let's consider this question separately for now, ok? Can we all be calm about this? :-) Therefore the query optimizer can perform every optimization that an application using ascynchronous SQL can. In addition, it can perform at least two more optimizations: it can overlap queries from different applications, and it can overlap different parts of an individual query. And it can do all this without increasing application complexity or decreasing safety. Of course, the user can subvert this by submitting two inherently sequential queries in separate processes, but that's true now, right? I'm not arguing here for increased safety, just for preserving what we have. Right now it's lexically apparent from any query language script in what order statements get executed, and I'd kind of like to keep that. -- Jon -- Jonathan Krueger jkrueger@dtic.dla.mil uunet!dgis!jkrueger The Philip Morris Companies, Inc: without question the strongest and best argument for an anti-flag-waving amendment.