Xml-Rpc + Mysql.NET = Threading problem

[# Cyrille37 #]

Hello all,

I come to you to getting help for managing multi threading and database connection.

My project use Xml-Rpc to receive messages, so each call come from a different
thread.
Incoming calls are executing SQL on a MysqlConnection .
MysqlConnection does not like when concurents calls appends.

For a fast and dirty solution, I've put a Monitor() at messages arrived.
But I would like to finally got a nicer solution.

What do you think about the architecture I should make ?

Should I create and manage a Pool of MysqlConnection and only locking when pool
is fully occuped ?

Do you know already made solutions for that case ?

Thanks a lot for you comments and ideas,
cyrille.

PS: Before I put the ugly Monitor, I got some errors like :

Exception: Connection must be valid and open. StackTrace:
at MySql.Data.MySq lClient.MySqlCo mmand.CheckStat e()
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader(CommandBeh avior behavior)
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader()

Exception: Expected prepared statement marker. StackTrace:
at MySql.Data.MySq lClient.NativeD river.Prepare(S tring sql, String[] parmNames)
at MySql.Data.MySq lClient.MySqlCo mmand.Prepare()

[Lloyd Dupont]

what's wrong with the Monitor?

Anyway it's very typical and advised to have a connection pool which (the
pool) as a properly synchronized access and return a private connection on
demand.
In fact SqlConnection (for SqlServer), does it for you under the hood.

Otherwise you could open a new connection for each incoming message..

One thing you should NOT do is use the same connection at the same time in
different thread.

"# Cyrille37 #" <cy*******@free .fr> wrote in message
news:up******** ******@TK2MSFTN GP11.phx.gbl...

Hello all,

I come to you to getting help for managing multi threading and database
connection.

My project use Xml-Rpc to receive messages, so each call come from a
different thread.
Incoming calls are executing SQL on a MysqlConnection .
MysqlConnection does not like when concurents calls appends.

For a fast and dirty solution, I've put a Monitor() at messages arrived.
But I would like to finally got a nicer solution.

What do you think about the architecture I should make ?

Should I create and manage a Pool of MysqlConnection and only locking when
pool is fully occuped ?

Do you know already made solutions for that case ?

Thanks a lot for you comments and ideas,
cyrille.

PS: Before I put the ugly Monitor, I got some errors like :

Exception: Connection must be valid and open. StackTrace:
at MySql.Data.MySq lClient.MySqlCo mmand.CheckStat e()
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader(CommandBeh avior
behavior)
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader()

Exception: Expected prepared statement marker. StackTrace:
at MySql.Data.MySq lClient.NativeD river.Prepare(S tring sql, String[]
parmNames)
at MySql.Data.MySq lClient.MySqlCo mmand.Prepare()

[# Cyrille37 #]

Hi Lloyd,

Lloyd Dupont a écrit :

what's wrong with the Monitor?
I loos multi-threading processing of incoming messages.
Anyway it's very typical and advised to have a connection pool which (the
pool) as a properly synchronized access and return a private connectionon
demand.
In fact SqlConnection (for SqlServer), does it for you under the hood.
That's Great.
I've to find or write a connections pool for MySqlConnection ...
Perhaps someone already done it ?
Otherwise you could open a new connection for each incoming message..
I think it will be to heavy.
One thing you should NOT do is use the same connection at the same timein
different thread.
Thanks for your advise. I'll be care ;o)

Thanks a lot for your councils.
Regards,
cyrille.

"# Cyrille37 #" <cy*******@free .fr> wrote in message
news:up******** ******@TK2MSFTN GP11.phx.gbl...

Hello all,

I come to you to getting help for managing multi threading and database
connection.

My project use Xml-Rpc to receive messages, so each call come from a
different thread.
Incoming calls are executing SQL on a MysqlConnection .
MysqlConnection does not like when concurents calls appends.

For a fast and dirty solution, I've put a Monitor() at messages arrived.
But I would like to finally got a nicer solution.

What do you think about the architecture I should make ?

Should I create and manage a Pool of MysqlConnection and only locking when
pool is fully occuped ?

Do you know already made solutions for that case ?

Thanks a lot for you comments and ideas,
cyrille.

PS: Before I put the ugly Monitor, I got some errors like :

Exception: Connection must be valid and open. StackTrace:
at MySql.Data.MySq lClient.MySqlCo mmand.CheckStat e()
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader(CommandBeh avior
behavior)
at MySql.Data.MySq lClient.MySqlCo mmand.ExecuteRe ader()

Exception: Expected prepared statement marker. StackTrace:
at MySql.Data.MySq lClient.NativeD river.Prepare(S tring sql, String[]
parmNames)
at MySql.Data.MySq lClient.MySqlCo mmand.Prepare()

[# Cyrille37 #]

Lloyd Dupont a écrit :

Hi Cyrille37

what's wrong with the Monitor? I loos multi-threading processing of incoming messages.

err.... could you reword this sentence?
I don't manage to guess its meaning....

Sorry for my bad english. I use my extra ball to try again :

With Monitor() at messages arriving, I lose the advantage of multi-threading.

Is that more readable ?
;o)
I have something like that (as an attached document of this answer).
Well it was a useless peace of code as SqlConnection already use a
connection pool under the hood.
But if you replace all the SqlConnection stuff by MySqlConnection it will
suddenly start to make sense!

THANKS A LOT !!!

Ok, I'll try it this afternoon.

<;oP> I hope you've make a non buggy code </;oP>

I give you a report after tests...

Cheers,
cyrille

[Lloyd Dupont]

>>> I loos multi-threading processing of incoming messages.

err.... could you reword this sentence?
I don't manage to guess its meaning....
Sorry for my bad english. I use my extra ball to try again :

With Monitor() at messages arriving, I lose the advantage of
multi-threading.

Is that more readable ?
;o)

Oh.. I see... right!

I have something like that (as an attached document of this answer).
Well it was a useless peace of code as SqlConnection already use a
connection pool under the hood.
But if you replace all the SqlConnection stuff by MySqlConnection it will
suddenly start to make sense!

THANKS A LOT !!!

Ok, I'll try it this afternoon.

<;oP> I hope you've make a non buggy code </;oP>

I hope too! ;-)

I give you a report after tests...

Cheers,

Yep, do tell me! Hopes it will work great!

[# Cyrille37 #]

Lloyd Dupont a écrit :

what's wrong with the Monitor?

Anyway it's very typical and advised to have a connection pool which (the
pool) as a properly synchronized access and return a private connectionon
demand.

What about the Prepared Statements ?

I think they are associated with a SqlConnection.
So If I create some prepared statements they will stay associated with the
connection but I could not use them if Pool give me another connection ...

I'm not clear because I could not imagine how Prepared Statement will be managed
when using a ConnectionPool.

What do you think about that ??

thanks
cyrille.

[# Cyrille37 #]

Lloyd Dupont a écrit :

I have something like that (as an attached document of this answer).
Well it was a useless peace of code as SqlConnection already use a
connection pool under the hood.
But if you replace all the SqlConnection stuff by MySqlConnection it will
suddenly start to make sense!

Yeah !
Your code works fine.

I've just made one change that do not close connections when freeing them, to
avoid the open/close overload.
Connections will be closed by MySqlConnection .Dispose() in MySql.Data.

Another stuff appends to me. While reading the source code of MySql.Data to be
shure connections will be closed, I found that there is already a Connection
Pool in the connector... But I did not know because the documentation does not
talk about...

I don't know if I still need to use the DBConnectionPoo l ...
By the way, it was fun to test your code ;oP

Cyrille.

[# Cyrille37 #]

# Cyrille37 # a écrit :

Lloyd Dupont a écrit :

I have something like that (as an attached document of this answer).
Well it was a useless peace of code as SqlConnection already use a
connection pool under the hood.
But if you replace all the SqlConnection stuff by MySqlConnection it
will suddenly start to make sense!

Yeah !
Your code works fine.

I've just made one change that do not close connections when freeing
them, to avoid the open/close overload.
Connections will be closed by MySqlConnection .Dispose() in MySql.Data.

Another stuff appends to me. While reading the source code of MySql.Data
to be shure connections will be closed, I found that there is already a
Connection Pool in the connector... But I did not know because the
documentation does not talk about...

I don't know if I still need to use the DBConnectionPoo l ...
By the way, it was fun to test your code ;oP

I did some stress tests with the DBConnectionPoo l and without it.

Result are very very faster with DBConnectionPoo l (my version without Closing).

Cyrille

[Lloyd Dupont]

>> I don't know if I still need to use the DBConnectionPoo l ...

By the way, it was fun to test your code ;oP

I did some stress tests with the DBConnectionPoo l and without it.

Result are very very faster with DBConnectionPoo l (my version without
Closing).

Good!
I guess you'll keep using it! ;-)

[Lloyd Dupont]

I would say, don't reuse them.
Create them on demand each time.
Not only it will make easier to maintain/use/understand, but I dont believe
there is that much to gain by trying to reuse them....

"# Cyrille37 #" <cy*******@free .fr> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Lloyd Dupont a écrit :

what's wrong with the Monitor?

Anyway it's very typical and advised to have a connection pool which (the
pool) as a properly synchronized access and return a private connection on
demand.

What about the Prepared Statements ?

I think they are associated with a SqlConnection.
So If I create some prepared statements they will stay associated with the
connection but I could not use them if Pool give me another connection ...

I'm not clear because I could not imagine how Prepared Statement will be
managed
when using a ConnectionPool.

What do you think about that ??

thanks
cyrille.