Thread problem

[Former Member]

Greetings!

I am a beginner at Powerbuilder .NET  and I have some concerns about threading. We are migrating an application from PB7 to PB12.5. It's a 3-tier application.

I have created a WCF Service and,  in order to understand the way threads work, I've coded a very simple function, with a very basic task, which is to insert some rows in a table. Just for didatic matters, I've decided to call four instances of this function, simulating  multithreading. In this case, there would be 4 threads accessing and populating the table for me. It's not the client who's gonna do that, but WCF Service itself. This is necessary due to the fact that in my company there are a lot of data to be processed by the WCF Service and this small function would be a simple lab to work as reference eventually.

So, here is the code for the main function:

long ll_conta

System.Threading.Thread l_thread

System.Threading.ThreadStart l_proc_thread

lproc_thread = uf_insert

For ll_conta = 1 to 4

      l_thread = create System.Threading.Thread(l_proc_thread)

      l_thread.IsBackground = true

      l_thread.Start()

      l_thread.sleep(500)

Next

return 1

And here is the code for uf_insert() function:

long ll_conta

datetime ldt_data

uf_connect_db()

For ll_conta =1 to  10000

      ldt_data = datetime(today())

   INSERT INTO CAD_THREAD (ID_THREAD, DS_NOME, DT_DATA) VALUES ( :ll_conta,'THREAD_TEST', :ldt_data);

Next

disconnect using sqlca;

return

As you can see it's a very simple routine, just for practicing.

The problem is, when I run the function, the 4 threads start running perfectly, but then they and stop before complete the For..Next statement.

When I take a look at my database I notice that only one thread inserts the 10000 rows in my table. As soon as this thread finishes and stop running, the other 3 stop as well.

Could  you please help me find a solution?

Thank you.

Deise

[Former Member · Accepted solution]

SQLCA is a global variable.  When the first thread finishes, wouldn't it disconnect SQLCA from the database and basically cut off any other threads using the same connection?

[Former Member · Accepted solution]

Hi, Bruce.

I took off the commandline 'disconnect using sqlca;', but it seems to be a little  trickier, because the remaining threads are stopping yet.

I've considered switching the database connection to the main function, but then the uf_insert() function doesn't recognize the connection.

Although it's a global variable, perhaps each thread holds its own  instance of the sqlca variable, doesn't it?

Thanks!

[Former Member · Accepted solution]

Try this:

• use a local transaction object within the method
• create the connection within the method, instead of through another method
• commit before you disconnect from the database
• disconnect from the database within the method

[Former Member · Accepted solution]

Hi, Bruce.

Problem solved!

Here is the final code for uf_insert() function:

=================================

long ll_conta

datetime ldt_data

Transaction My_sqlca

My_sqlca = create Transaction

My_sqlca.logid = '<SomeId>'

My_sqlca.logpass='<SomePassword>'

My_sqlca.dbms = 'ADO.Net'

My_sqlca.AutoCommit = true

My_sqlca.DBParm = "Disablebind=1,Namespace='System.Data.SqlClient',DataSource='<MyServer>',Database='<MyDatabase>'"

connect using My_sqlca;

For ll_conta =1 to  10000

      ldt_data = datetime(today())

   INSERT INTO CAD_THREAD (ID_THREAD, DS_NOME, DT_DATA) VALUES ( :ll_conta,'THREAD_TEST', :ldt_data) using My_sqlca;

Next

disconnect using My_sqlca;

return

=================================

By using a local transaction object, the four threads were able to run completely up to the end.

I was really not aware of the database connection matter.

Thank you very much for your help, I hope I might also be able to contribute with this forum eventually.

[Former Member · Accepted solution]

Guys!

I was wondering:

I have a process I may or may not run by thread.

This means I don't always need to create a transaction object.

It's up to my users to set the number of threads they wish.

In this case I can see a problem concerning the SQLCA.

The objects involved have functions inherited, which uses SQLCA widely.

I would have to rewrite the code all over the system in order to consider MySqlca, and I still would have to consider both situations ( SQLCA and MySqlca ).

I have tried many approaches, but I can't see a clear solution that avoids reworking and gives me a neat code.

Do you have any idea to make a flexible set of routines?

Thank you.

[Former Member · Accepted solution]

Hi Deise;

  First of all WHOA!!!!!!!!!  Never, never, never (and if in doubt never) use any global variables in a multi-threaded application as you will inadvertently make the application single threaded. SQLCA is a prime example of a global variable!

  Only use instance variables in each thread.

Regards ... Chris

[Former Member · Accepted solution]

Hi Deise,

My first thought is you could overload your functions with a new set of functions that take a transaction object as an argument. The processes that need to be multithreaded could pass in MySqlca to the overloaded functions. The processes that don't could use the existing functions.

hth,

Mark

[Former Member · Accepted solution]

The issue here is your application design and the approach you use to write your "process".  You have a process and that process needs a transaction object.  Rather than assume and use SQLCA, you simply need to provide a mechanism to control (or direct) the process to use a particular transaction object.  With that approach, you can tell the process to use SQLCA or any other transaction object you care to create.  Of course, you could also provide a mechanism that allows your process to create and manage its own transaction object.  In PFC, you will find many classes have an of_settransobject function to do exactly that.

[Former Member · Accepted solution]

Hi, Chris.

Yeah, I'm aware of that!

Never, ever global variables...

My problem is that there are  routines that switch between single and multi threading.

I believe that Mark has a good idea by suggesting  a new argument in my functions...

Unfortunately it's going to be a very rough path ....  Once again:  "no pain, no gain"

[Former Member · Accepted solution]

Hi, Mark.

Thank you. That's an idea...

[Former Member · Accepted solution]

As Scott suggested, take a look at some of the transaction related features of PFC.  One of those, as Scott mentioned, is a transaction caching service.

Another is the of_copyto method on the transaction object (pfc_n_tr).  That allows you to easily create another transaction object with the same properties as the original.  So you could continue to use SQLCA as the definition of the transaction to use, but create local copies for actual use.

[Former Member · Accepted solution]

Hi, Scott.

You've mentioned the Achilles Heel of the application.

It's really a matter of design.

We even have a non visual object of transaction class type which is associated with SQLCA in the application properties/ variable types tab, it mostly  takes care of Dbparm and Oracle connection matters.

I am taking a look at PFC,  it's really something that might work for me.

Thank you!

[Former Member · Accepted solution]

Hi, Bruce.

I am taking a look at PCF and going back to the drawing board.

Thank you!