Context connection and transactions

All -
Facts:
(a) SqlConnection allows only one "Context connection" to be opened at a tim
e.
(b) SqlConnection doesnt support parallel transactions. (calling a second
BeginTransaction before committing the first BeginTransaction throws
exception)
Problem:
Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
registered as SQLProcedure, is invoked.
Inside this code (which is now in SqlServer context) - i open a
SqlConnection as "context connection".
All the SqlCommand objects that uses this context connection, need the same
transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
different transaction. (lets say TransB)
How to do this?
Calling BeginTransaction twice blows up.
Creating 2 seperate instances of SqlConnection with context connection =
true, also blows up.
Remember that somehow i might want to rollback TransA but always committ
TransB. For instance
try
{
// Do some actions
// SqlCommands that use TransA and TransB will be executed here
transA.Committ(); // things are fine - committ A
}
catch
{
transA.Rollback(); // things are incorrect - rollback A
}
finally
{
transB.Committ(); // Always comitt the logs...
}
How to do this when we are in context connection?
Regardz
Grafix."Grafix" <Grafix@.discussions.microsoft.com> wrote in message
news:54F45F4C-4F6B-44D7-8B13-0D55F13BB75C@.microsoft.com...
> All -
> Facts:
> (a) SqlConnection allows only one "Context connection" to be opened at a
> time.
> (b) SqlConnection doesnt support parallel transactions. (calling a second
> BeginTransaction before committing the first BeginTransaction throws
> exception)
> Problem:
> Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
> registered as SQLProcedure, is invoked.
> Inside this code (which is now in SqlServer context) - i open a
> SqlConnection as "context connection".
> All the SqlCommand objects that uses this context connection, need the
> same
> transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
> different transaction. (lets say TransB)
> How to do this?
> Calling BeginTransaction twice blows up.
> Creating 2 seperate instances of SqlConnection with context connection =
> true, also blows up.
> Remember that somehow i might want to rollback TransA but always committ
> TransB. For instance
>
You will have exactly the same issue in TSQL. SQL Server does not support
autonomous transactions.
A common workaround is to write data into a table variable (or List<T> ), and
do something with it after the transaction is rolled back.
David|||I haven't gone into CLR stored procedures too much. But can't you add this
one SqlCommand (LogCmd) that needs a different transaction
into a seperate class and use the transaction option as requiresNew and with
the method haveing an autocommit tag to be true. and then call it from the
parent transaction.|||> one SqlCommand (LogCmd) that needs a different transaction
> into a seperate class and use the transaction option as requiresNew
There is the problem.
I am not able to have 2 active transaction scopes at the same time.
Remember LogCmd will be executed multiple times inbetween other regular
xxxCmd.
Which means i want 2 active transaction scopes (each with Option
"RequiresNew")
Unfortunately when i am in context connection (C# sp), i am not allowed to
create 2 connections that i can enlist with the TransactionScope.
I tried ur suggestion with the new .NET 2.0's
System.Transactions.TransactionScope class (Promotable transaction) for
seeing if i can enlist a single connection with multiple transactions - but
the problem is the same. So bad that they have introduced new Options like
TransactionScopeOption.Suppress and i am not able to use it still.
With one SqlConnection - u cannot associate with 2 different transaction
scopes.
And this C# sps allow only one connection at a time.
Seems i have to follow David Browne's suggestion of in-memory datastructure
to hold the logs and committ at the end.

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hi Remus

What if I need multiple clients to read (RECEIVE) the same message?

Would it be possible?

Thanks

No.

A message can only be received once. Normally the first RECEIVE statement removes it from the queue, so no other RECEIVE can find the same message.

Also there is no way for the clients to specify the message to be received. With a WHERE clause the RECEIVE statement at most can restrict the result set to a particular conversation, but not to a particular message.

And finally RECEIVE statement is always executing in READ COMMITED isolation level, so two clients cannot receive messages from the same conversation group in different transactions, since each RECEIVE will attempt to place an exclusive lock on the conversation group and only one transaction can have an exclusive lock at any given moment.

HTH,
~ Remus

|||

If you are looking at a publish/subscribe type scenario, where you want messages to be delivered to multiple services, you could implement a service that maintains a list of subscriber services and upon receiving a message, sends a copy out each of its subscribers. See the sample on Remus' blog:

http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx

|||

Hi Rushi/Remus

I tested that example, setting the same subscription from two different clients. Then I sent a publication, and read messages.

For what I understand in that example when a client subscribes for a particular publication, his conversationID is saved on a table.

When a publication occurs a procedure sends messages to all subscribers, using that conversationID.

BUT, in case there are two subscriptions and a single client is listening for messages, two identical messages are read by the client.

In case there are two clients listening a lot of confusion, sometimes one client gets two messages, sometimes one, sometimes nothing...

I was expecting , since the conversationID seems to address to a single endpoint, only one message...

|||

Assuming that on a publish/subscribe scenario each client must create a unique subscription, I realize that each client have to create its own queue and service.

The problem is sending messages then.

The initiator should send the same message to all queues, but how? The number of queues created is not defined, is there a way to do it?

Is my theory correct? Or am I on the wrong direction?

Thanks for helping

|||

The subscribers are individual conversations. If they are on the same queue, then you must use the RECEIVE ... FROM queue WHERE conversation_handle = ... syntax to retrieve only the notifications for a given client (subscription).

If you use the RECEIVE w/o a WHERE clause, then the clients will mix the notifications, if they are on the same queue.

In the pub/sub sample at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx the initiator doesn't know nor need to how many clients/queues are there. It will iterate through subscriptions and send a message to each one. Clients can be on the same queue or on different queue, it doesn't matter. The subscription notifications are all reply messages (from target to initiator, since is the client that initiates the subscription), so the pub/sub service does not need to know upfront how many clients are there, it just sends replies on the existing dialogs.

HTH,
~ Remus

|||

Thanks for the clarification Remus.

Now it's working good!

|||

Hi,

I have a couple of questions to make.

How can i trigger notifications to my application ( C#)

without hanging in WaitFor Operation?

It's possible for broker service to call some remote

object that my application provide ?

Can Publish/Subscribe using Broker Service be used

for a low latency notifications (150ms ) max with milions of

messages published per second?

Thanks in advance

Srgio

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hello All,

After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues.
Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.

We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?

Thank you very much,

Lubomir

P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;

Lubomir,

Retrieving messages from a queue with the RECEIVE statement should be regarded similar with running an UPDATE statement on a table. Multiple clients (Windows Services in you case) can run concurent updates (receives in your case) as long as they don't try to update the same rows (messages in your case). The difference is that in the RECEIVE case there is a built in mechanism to choose what rows should be updated (i.e. what messages should be dequeued) in order to avoid update conflicts. Each RECEIVE will grab the next available (i.e. not locked) conversation group, lock it, and then retrieve (dequeue) messages from conversations in this group. In fact, one can use any of the tools that show query plans (Profiler, Management Studio, Query Analyzer) and ask for the query plan of the RECEIVE in order to understand what this statement does.

So yes, RECEIVE statements can be issued in parallel and they will execute simultaneously.

There is an External Activator sample at you might want to take a look at, http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319.

HTH,
~ Remus

|||Thank you very much for the insight, Remus. The external activator seems rather promising.

Lubomir|||

Thanks

So if I understand well I should build a different conversation for each client, and replicate same messages on different conversation, so that every client gets the message.

But what if the number of clients isn't fixed?

Any suggestion?

|||

Basically this is a Publish/Subscribe scenario. Look at this example at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx and see if you can start building something from it.

HTH,
~ Remus