Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.
- Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
- Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
- Effective monitoring: People want to see which session is running what.
- Adjustable maximum threads in the course of execution.
I implemented those and posted on CodePlex, http://psql.codeplex.com. It’s very easy to use.
set xact_abort on begin transaction exec dbo.AsyncName @Name = 'Test' exec dbo.AsyncMaxThreads @MaxThreads = 3 ----Start: those queries will be executed by 3 threads exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''' exec dbo.AsyncWait -- wait commands above to complete commit -- async batch complete
The main session is responsible for starting an asynchronous batch, setting execution configurations, queuing tasks, waiting for batch completion, and stopping a batch.
set xact_abort on begin transaction --Code here... commit
An asynchronous batch must be started within a transaction. The transaction will not be propagated in to any asynchronous commands. When transaction is completed (commit or rollback), the batch will stop automatically, currently executing asynchronous commands will be cancelled and waiting commands will be aborted.
It’s recommended to set xact_abort on. This guarantees that when the main session is cancelled/killed, transaction will be rolled back automatically, hence batch will be cancelled. Procedures used in the first example
- AsyncName: Optionally you can give current batch a name.
- AsyncMaxThreads: Set max number of concurrent worker threads
- AsyncExecute: enqueue a T-SQL command. As long as a command is enqueued, it will be started immediately when there is an available worker threads
- AsyncWait: this procedure will wait all worker threads to complete.
One more example
set xact_abort on begin transaction exec dbo.AsyncName @Name = 'Test-1' exec dbo.AsyncMaxThreads @MaxThreads = 3 ----Start: those queries will be executed by 3 threads exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''' exec dbo.AsyncWait -- wait commands above to complete --commit --begin transaction exec dbo.AsyncName @Name = 'Test-2' exec dbo.AsyncMaxThreads @MaxThreads = 2 ----Start: those queries will be executed by 2 threads exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:35''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:25''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:15''' exec dbo.AsyncWait -- wait commands above to complete commit -- async batch complete
Batch name and max threads can be changed any time before a batch is finished. In example above, First 4 asynchronous commands will be run concurrently by 3 threads. After they are done, last 3 commands will be executed by 2 threads.
To splitting this batch into two, you can simply add commit after the first AsyncWait invocation and add begin transaction right after the commit statement you just added. This will not make difference in terms of asynchronous command executions. But it will show differently in the monitor.
While the first demo is running, run following query in a separate session
select * from dbo.AsyncTaskList()
Pass the main session id into AsyncTaskStatus function to get more details.
select * from dbo.AsyncTaskStatus(53)
Function AsyncTaskStatus will give you the statuses of each command in a asynchronous batch. This structure will stay in memory and attached to the main session id (53 in this case) until the next batch gets started(in session 53).
Run the first demo again, click on “Stop” in SSMS while the main session is waiting/running, and then check the results from function dbo.AsyncTaskStatus to see how it behaves.
select * from dbo.AsyncTaskStatus(53)
What if one of the workers generates an error? By default, executing commands will be cancelled, waiting commands will be aborted, and errors generated by the worker will be thrown by dbo.AsyncWait procedure
set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''; raiserror(''Error'', 16, 1)'
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete
Check both monitoring functions
select * from dbo.AsyncTaskList() select * from dbo.AsyncTaskStatus(53)
This behaviour can be adjusted by following procedure.
- Procedure AsyncCloseWhenException @CloseWhenException, @ForceClose
This procedure should be run within main session. It defines how the batch behave when there is an exception in one of the worker thread.
When @CloseWhenException is 0, exceptions in worker threads will not terminate the main session.
When @CloseWhenException is 1, exceptions in any worker threads will terminate the main session.
@ForceClose is applicable when @CloseWhenException=1. It tells the framework how to terminate the running workers. The commands in waiting list will be aborted regardless.
When @ForceClose is 0, main session will exit until all running workers complete
When @ForceClose is 1, running workers will be cancelled. Main session will return once the command cancellation is done
This procedure tells the framework what to do when there is an exception in a running worker. For instance, I want the process to continue when exception.
set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
exec dbo.AsyncCloseWhenException @CloseWhenException=0, @ForceClose=0
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''; raiserror(''Error'', 16, 1)'
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete
In this particular case, the main session will not return any errors. But the monitor will tell which session has error.
select * from dbo.AsyncTaskList() select * from dbo.AsyncTaskStatus(53)
An running asynchronous batch can be terminated by AsyncClose procedure.
- Procedure AsyncClose @spid, @ForceClose
This procedure allows user to terminate a running asynchronous batch. The commands enlisted in the batch but not started yet will be aborted.
@spid: main session’s session id.
When @ForceClose is 0, main session will exit until all running workers complete
When @ForceClose is 1, running workers will be cancelled. Main session will return once the command cancellation is done.
For an instance, re-run the first demo again
set xact_abort on begin transaction exec dbo.AsyncName @Name = 'Test' exec dbo.AsyncMaxThreads @MaxThreads = 3 ----Start: those queries will be executed by 3 threads exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20''' exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''' exec dbo.AsyncWait -- wait commands above to complete commit -- async batch complete
From another session, run following command
exec dbo.AsyncClose 53, 0 select * from dbo.AsyncTaskList() select * from dbo.AsyncTaskStatus(53)
The waiting session is aborted. the running session will keep going until it complete. Main thread/session will wait as well. What if @ForceClose is set
exec dbo.AsyncClose 53, 1 select * from dbo.AsyncTaskList() select * from dbo.AsyncTaskStatus(53)
The executing workers are cancelled and the waiting workers are aborted.
More implementations are coming
- Plan to support enlisting parameterized commands
- Plan to support call back events. For instance, a procedure can be configured and called before a command is started.
- Unload an executing or waiting task.
This project is posted on CodePlex, http://psql.codeplex.com. I am still improving it. Any ideas, suggestions, and questions you may have or you’d like to join the project, please contact me.
Brought you by http://sqlnotes.info
