Question Re: Thread problem in Technology Q&A

Thread problem

Former Member — Wed, 12 Jun 2013 21:39:40 GMT

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)

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);

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

Re: Thread problem

Former Member — Thu, 13 Jun 2013 03:57:01 GMT

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?

Re: Thread problem

Former Member — Thu, 13 Jun 2013 15:55:55 GMT

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!

Re: Thread problem

Former Member — Thu, 13 Jun 2013 17:23:22 GMT

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

Re: Thread problem

Former Member — Thu, 13 Jun 2013 19:31:37 GMT

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;

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.

Re: Thread problem

Former Member — Thu, 20 Jun 2013 20:52:35 GMT

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.

Re: Thread problem

Former Member — Fri, 21 Jun 2013 11:15:29 GMT

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

Re: Thread problem

Former Member — Fri, 21 Jun 2013 12:01:09 GMT

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

Re: Thread problem

Former Member — Fri, 21 Jun 2013 12:51:04 GMT

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.

Re: Thread problem

Former Member — Fri, 21 Jun 2013 14:00:26 GMT

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"

Re: Thread problem

Former Member — Fri, 21 Jun 2013 14:22:16 GMT

Hi, Mark.

Thank you. That's an idea...

Re: Thread problem

Former Member — Fri, 21 Jun 2013 14:39:07 GMT

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.

Re: Thread problem

Former Member — Fri, 21 Jun 2013 16:29:43 GMT

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!

Re: Thread problem

Former Member — Fri, 21 Jun 2013 16:34:22 GMT

Hi, Bruce.

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

Thank you!