|
<< Click to Display Table of Contents >> Navigation: Telegram > 2020 > 05 > 18 > Telegram_2020-05-18T06 |
2020-05-18T06:05:22
Exactly and my concern is what would happen if from the 20 sessions, all update the same database that has only one connection at the same time. My guess is that at the database side, Intraweb has no more control and that’s why I thought queuing the entry from session with TCriticalSession in the class that execute the CRUID
2020-05-18T06:06:49
The DB connection per session no problem but at the end these connections will try to modify one database
2020-05-18T06:10:32
I have witnessed a manufacturing system from a big software company crashing all the time because different threads from different sensors trying to modify SQLserver database and deadlocking each other
2020-05-18T06:18:15
Not good at drawing but I hope you will understand what I am talking about
2020-05-18T06:30:08
OK, if you are trying to have all sessions use a single connection, you would have to put the db connection on the ServerController — which is a very bad idea
2020-05-18T06:32:20
If you truly want a single DB connection, and your architecture supports such a thing — then you should still used Pooled Connections — set the pool size to 1, and set the Pool itself so that it cannot grow. Then use use MyDM := LockDataModule; when you want to use the connection, and UnlockDataModule(MyDM) when you are done. You will never need to work about thread clashing if you do this
2020-05-18T06:33:20
Then they did a horrible job at designing their system
2020-05-18T06:33:42
My connection is on UserSession and it represents your drawing; what would then happen once all these connections try to modify the database?
2020-05-18T06:35:01
Exactly the same thing as if it were separate desktop applications on different computers on the network — and all send SQL statements to the server at the same time
2020-05-18T06:35:07
It's not an IW issue
2020-05-18T06:37:42
What I have done is having one class in Intraweb that is marshaling the entry into database.
2020-05-18T06:38:56
In my big IW app — I use data connection pooling. My PoolSize is set to 20, and I allow it to grow as needed. Those 20 connections service thousands of concurrent users. It works brilliantly, because each session needs to use a connection on average once every two minutes, and when a session needs a connection — it grabs one, finishes its business very, very quickly, and releases the connection back to the pool.
2020-05-18T06:39:11
For what purpose?
2020-05-18T06:39:31
It sounds like a whole lot of work for no reason
2020-05-18T06:40:01
Thanks for your advice!
2020-05-18T06:40:33
The worst case scenario I can think of is where you would have one session create multiple threads. But even in this case — you should still use DB Connection Pooling. Each thread could lock a connection as needed
2020-05-18T06:41:43
My App does not use connection pooling; any idea how to modify and bring it in?
2020-05-18T06:42:05
I couldn't tell you off the top of my head, but it's doable
2020-05-18T06:42:17
Ok
2020-05-18T06:42:38
However, to make it clean — I would just create a new application with the option for pooled db connections, and then add all the units from the old project in
2020-05-18T06:43:08
and of course replace the new ServerController and UserSession with your old versions
2020-05-18T06:43:40
You would have to cut and paste your db connection off of your old UserSession and put it on the Pooled DataModule
2020-05-18T06:44:54
and then on your pages you would use the following code anytime you need a connection:
2020-05-18T06:46:27
procedure BlahBlah;
var
MyDM: TMyDM;
begin
MyDM := LockDataModule;
try
MyDM.qOrders.ParamByName('WHATEVER').AsInteger := 123;
MyDM.qOrders.Open;
DoXYZ;
MyDM.qOrders.Close;
finally
UnlockDataModule(MyDM);
end;
2020-05-18T06:47:05
Ok thanks
2020-05-18T06:47:28
welcome
2020-05-18T06:48:54
If you have a maintenance screen is rarely accessed, you could even Lock a pooled connection OnCreate, and UnLock it OnDestroy, so that you could use a DB grid, or other components that need a stable, open dataset.
2020-05-18T06:49:39
In short — you can do anything with pooled db connections that you can do by putting a db connection on each UserSession — but with a small fraction of the number of connections
2020-05-18T06:49:52
I think that’s the best
2020-05-18T06:50:57
and you never need to worry about thread clashing or TCriticalSection
2020-05-18T06:51:22
Thank you so much Loren!
2020-05-18T06:51:29
anytime