Can anyone tell me the differences between Asynchronous and Synchronous transactions... I am using VB6 to access our SQL 7 server... but I am new to this, and need to decide whether or not we want sync or async transactions...

Thanx, Eric

-- Anonymous, March 10, 1999

Answers

Eric,

I found these articles in SQL Server Books Online.

ASYNCHRONOUS QUERY PROCESSING Preemptive multitasking in the Win32 API makes it easy to implement asynchronous query processing. Use one of the following ways to implement asynchronous query processing, depending on whether you want your application to exhibit asynchronous behavior between processes or within a single process.

7 Switch tasks between processes. Use the standard dbsqlexec call to send a query to SQL Server. Although dbsqlexec is synchronous from the calling thread's perspective (dbsqlexec returns only when SQL Server processes the query and is ready to return results), the preemptive nature of the Win32 API allows other applications or threads of the same application to continue to work and process user input while the query is executing.

7 Implement asynchronous processing within a single process. Use threads. The easiest way to implement asynchronous processing within a process is to use Win32 threads. You can spawn a thread that calls dbsqlexec and continue to do other work or continue to receive user input while the query is being processed. Single-thread approach. If you require a single-thread process to implement asynchronous query processing, use the asynchronous DB-Library functions dbsqlsend, dbdataready, and dbsqlok in combination with the PostMessage function, as shown in this example:

case WM_SENDQUERY: dbsqlsend(dbproc); PostMessage(hWnd,WM_CHECKQUERY,0,0L); break;

case WM_CHECKQUERY: if (dbdataready(dbproc)) { dbsqlok(dbproc); PostMessage(hWnd,WM_GETRESULTS,0,0L); } else { PostMessage(hWnd,WM_CHECKQUERY,0,0L); } break;

ASYNCHRONOUS TRANSACTION COMMIT AND ABORT Usually, ITransaction::Commit or Abort calls are performed synchronously. When an application calls ITransaction::Commit, the calling thread in the application blocks until the end of phase one of the two-phase commit protocol. When an application calls ITransaction::Abort, the calling thread in the application blocks only briefly. Control is returned to the application as soon as the commit coordinator is notified that the transaction should be aborted.

To entirely avoid blocking the calling thread, use asynchronous Commit or Abort calls. To call Commit asynchronously, specify XACTTC_ASYNC for the grfTC parameter. To call Abort asynchronously, specify TRUE for the fAsync parameter.

For more information about asynchronous Commit and Abort calls, see ITransaction in Chapter 5, "MS DTC Programmer's Reference."

Hope this helps,

Eric

-- Anonymous, March 12, 1999