Click here to Skip to main content
14,739,371 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
public void enqueue(object e)
        DataChangeEventArgs e113 = (DataChangeEventArgs)e;
        for (i=0; i < e113.sts.Length; i++)
            int hour113 = e113.sts[i].TimeStampNet.Hour;
            int minute113 = e113.sts[i].TimeStampNet.Minute;
            int second113 = e113.sts[i].TimeStampNet.Second;
            int millisecond113 = e113.sts[i].TimeStampNet.Millisecond;
            int year113 = e113.sts[i].TimeStampNet.Year;
            int month113 = e113.sts[i].TimeStampNet.Month;
            int day113 = e113.sts[i].TimeStampNet.Day;
            DateTime sdate113 = new DateTime(year113, month113, day113, hour113, minute113, second113, millisecond113);

            DataRow row1 = dt.NewRow();
            row1["itemID"] = e113.sts[i].HandleClient;
            row1["paramvalue"] = convert.ToString(e113.sts[i].DataValue);
            row1["date_logged1"] = sdate113.ToString("dd-MM-yyyy HH:mm:ss.fff");
            row1["Quality"] = e113.sts[i].Quality;
            row1["date_logged"] = DateTime.FromFileTime(e113.sts[i].TimeStamp);
        var threadupdate = new Thread(update);//start new thread & instance
    catch { }

public void update(object dtnew)
                   MySqlConnection con = new MySqlConnection(LocalConnection.GetLocalConnetionStringmysql());
                    DataSet oldvalueds=new DataSet();
                    DataTable newupdateddata = new DataTable();
                    MySqlDataAdapter da;
                    MySqlTransaction trans;
                    if (con.State == ConnectionState.Closed)
                        con.Open();//throws exception
                    trans = con.BeginTransaction();
                    da = new MySqlDataAdapter();
                    da.InsertCommand = new MySqlCommand("INSERT INTO parameter (itemID,paramvalue, date_logged1,Quality,date_logged) " +
                                           "VALUES (@itemID, @paramvalue,@date_logged1,@Quality,@date_logged)" +
                                           "ON DUPLICATE KEY UPDATE " +
                                           "itemID=VALUES(itemID),paramvalue=VALUES(paramvalue),date_logged1=VALUES(date_logged1),Quality=VALUES(Quality),date_logged=VALUES(date_logged)", con);

                    da.InsertCommand.Parameters.Add("@itemID", MySqlDbType.VarChar, 250, "itemID");
                    da.InsertCommand.Parameters.Add("@paramvalue", MySqlDbType.VarChar, 250, "paramvalue");
                    da.InsertCommand.Parameters.Add("@date_logged1", MySqlDbType.VarChar, 250, "date_logged1");
                    da.InsertCommand.Parameters.Add("@Quality", MySqlDbType.VarChar, 250, "Quality");
                    da.InsertCommand.Parameters.Add("@date_logged", MySqlDbType.VarChar, 250, "date_logged");
                   da.InsertCommand.Transaction = trans;
                     DataTable newupdateddt = (DataTable)dtnew;
                  int k = da.Update(newupdateddt);//exception thrown 2nd

            catch(Exception ex) {


It will be thrown an exception like "Error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

I will be set the timeout=200 in connection string this exception was thrown
"Deadlock found when trying to get lock; try restarting transaction"

Thanks in advance
Updated 30-May-11 4:00am

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

This error may also cause because u may not have saved ur database at proper location.

I think firstly set timeout to Default. Then save your database at proper location.Close ur database and then try to check ur application for Update query.This may solve ur timeout problem

Amul V V Wayangankar
please check your database connection and set proper timeout... I think it is set to default...
vrushali katkade 30-May-11 8:11am
please see my updates
Try to disable Connection pool and see how your code is working.


Perhaps change Connection Lifetime or Max Pool Size

See link from MySql about connection string that is valid.[^]
vrushali katkade 31-May-11 0:30am
when i will be set pooling in connection string it will be throws exception "Input string was not in a correct format."
Kim Togo 31-May-11 2:19am
Strange. Can you post the connection string you are using?
Kim Togo 31-May-11 2:23am
What version of MySql Connector/Net are you using ?
vrushali katkade 31-May-11 2:29am
in connection string i will be set the pool size & then set the pooling=false ,this exception solved but new exception is coming at
int k = da.Update(newupdateddt);this line "too many connections"
Kim Togo 31-May-11 2:43am
Okay, then I think that the problems is at the server end. In MySql you can limit the maximum connection.
But then, maybe you should rethink your design. Right now your flow is:

Connect -> Begin Transaction -> Update -> End Transaction -> Close

And if you do this many many time very fast, then you create a huge amount of connection to MySql.

On MySql server, try run a "SHOW PROCESSLIST" then you can see how many connections you have.
vrushali katkade 31-May-11 3:06am
i will be check the list 101 connections are in the list
vrushali katkade 31-May-11 3:14am
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
solution on tis exception actually when the connection beyond the 101 connection it will be thrown exception too many connection . but when i will be create the new instance of the function of update using the thread then when the updates function finished the work it will be release the connection then why this problem is coming
vrushali katkade 31-May-11 3:19am
when i will be check for one connection it will be add the two process connection in process list why this will be happed.
vrushali katkade 31-May-11 3:39am
please help me
Kim Togo 31-May-11 3:41am
What version of MySql Connector/Net are you using ?
vrushali katkade 31-May-11 3:50am
Kim Togo 31-May-11 4:01am
That is an old driver. The latest version is version 6.3.6 -

And I know there has been bugfixs about connection pool not releasing connection to mysql etc..
If I where you, I would upgrade to version 6.3.6.
vrushali katkade 31-May-11 5:32am
i will be download the version 6.3.6 but it will be display the same exception
string ConnectionString = "Server=localhost;" +
"UID=root;" +
"DataBase=globasys;" +
"Allow Zero Datetime = false;" +
"Convert Zero Datetime=true;" +
my connection string given above
vrushali katkade 31-May-11 5:52am
any suggestion about this when i will be delete the pooling=false then it will be shown another exception as "error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." it will be increased the size of the processlist
Kim Togo 31-May-11 5:54am
Try to set Connection Lifetime=2
It seams that connections to MySql is never closed and you hit the maximum connection allowed by the MySql.
vrushali katkade 31-May-11 6:48am
i will be set the Max_connections =200 & command timeout =90
it will be take the process list greater than 101 but after some time it will be show another exception as "lock wait timeout exceeded try restarting transaction"
vrushali katkade 31-May-11 6:57am
how to set the innodb_lock_wait_timeout in MYSQL
Kim Togo 31-May-11 6:59am
Is it necessary to start a transaction ? You only have 1 SQL statement.
Kim Togo 31-May-11 7:04am
How to change innodb_lock_wait_timeout
vrushali katkade 31-May-11 7:01am
the all process will be execute at every 1 seconds
Kim Togo 31-May-11 7:13am
Try remove transaction.
vrushali katkade 31-May-11 7:56am
i will be use this transaction for improve the performance of the int k = da.Update(newupdateddt); function because when the rows are increased into the datatable then it takes lots of time to update the table using the transaction it will be update the table within millisecond so i want speedup the performance also
Kim Togo 31-May-11 8:13am
I think the main problem is, that you have to many connection to MySql and resource is not free up again.

Perhaps a better design is to queue all SQL statements in a ConcurrentQueue<T>, and have 1 connection that takes from that queue and execute the SQL statements.
vrushali katkade 31-May-11 8:14am
can please tell how to improve the performance of the dataadapter.update() without transaction
vrushali katkade 31-May-11 8:16am
but it will be takes lots of time to dequeue i wants to update the at every 250 millisecond upto the 128000 rows
Kim Togo 31-May-11 9:08am
That is many update :-)
It still think you have to redesign how you what to update the database.

If it was me, I will make 1 thread that has 1 connection to MySql. And when there is data in the ConcurrentQueue≶T>, then dequeue it and update database.

The one that feeds the ConcurrentQueue≶T> only has to enqueue a SQL statement.
vrushali katkade 31-May-11 8:22am
it will not update the #innodb_lock_wait_timeout = 500

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900