<p id="rrtt5"></p><ruby id="rrtt5"></ruby>

      系統城裝機大師 - 固鎮縣祥瑞電腦科技銷售部宣傳站!

      當前位置:首頁 > 數據庫 > Mysql > 詳細頁面

      mysql 數據庫鏈接狀態確認實驗(推薦)

      時間:2022-10-02來源:www.1999hs.com作者:電腦系統城

      1.起因:

      在做一個小系統的時候,我想數據量交互不大,就都用一個鏈接執行算了,還能節省點資源,但是實際情況遇到了如下問題,在使用的過程中,有些數據操作會被轉移到其他線程,這樣;我這個簡單的想法遇到了問題,因為一個現場在使用這個鏈接的時候,其他線程也會同步插入執行數據操作,這樣一個鏈接就會面臨共用的沖突,怎么辦呢,有如下三種方案:

      1.1.數據兩次一次一聯,一用,一釋放。

      1.2.強制是數據庫的執行放到一個現場,那么得把所有的執行參數放到隊列中,有數據支持的線程按照隊列的順序執行。也可以在使用的時候把鏈接索起來。這樣強制的使數據的處理串行。

      1.3.做一個內部的鏈接對象使用池,池中的對象可供服用,解決重復鏈接的問題,提供多個對象解決現場使用沖突的問題。

      我選擇了方案三,做了如下實驗,記錄如下

      2.實驗

      2.1 確認數據鏈接狀態,使用完的數據鏈接是什么狀態呢?

      2.1.1 目的:我想根據鏈接的狀態判斷鏈接是在使用中,還是可以給別人使用;就是在可以使用的情況下,我就用該鏈接執行,否則使用或者創建其他的鏈接。

      代碼。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      using System;
      using System.Data;
        
      namespace 數據庫鏈接狀態確認
      {
          /// <summary>
          /// 確認數據庫通常執行完成是什么狀態
          /// </summary>
          public class Test1:Singleton<Test1>
          {
              public void main()
              {
                  test1();
              }
              private void test1()
              {
                  DBmsqlSub dBmsqlSub = new DBmsqlSub();
                  string sql = "insert into tb1(v1) values(2)";
                  dBmsqlSub.ExecuteNonQuery(sql);
                  ConnectionState connectionState = dBmsqlSub.getState();
                  Console.WriteLine(connectionState);
                  dBmsqlSub.Close();
              }
          }
      }

      結果,整理使用后的鏈接狀態是open,那是不是open的時候就可以供別人使用了呢,要是在執行的時候也是open狀態,那我就沒有判定已經了。

      1
      2
      3
      4
      數據庫鏈接狀態確認
      id0
      index:1 isuser:False setuser:False
      Open

      2.2 數據庫在執行的時候,是不是有一個執行中的狀態呢?

       

      代碼

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      using System;
      using System.Threading.Tasks;
        
      namespace 數據庫鏈接狀態確認
      {
          /// <summary>
          /// 目的:確認執行中的數據狀態
          /// </summary>
          class Test2:Singleton<Test2>
          {
              public void main() {
                  DBmsqlSub dBmsqlSub = new DBmsqlSub();
                  Task.Factory.StartNew(test2Exe, (Object)dBmsqlSub);
                  Task.Factory.StartNew(test2Exe, (Object)dBmsqlSub);
                  Task.Factory.StartNew(test2State, dBmsqlSub);
              }
              private void test2Exe(object dBmsqlSub)
              {
                  int index = 1;
                  while (true)
                  {
                      index++;
                      string sql = "insert into tb1(v1) values(" + index + ")";
                      ((DBmsqlSub)dBmsqlSub).ExecuteNonQuery(sql);
                      Task.Delay(200);
                  }
              }
              private void test2State(object dBmsqlSub)
              {
                  while (true)
                  {
                      Console.WriteLine(((DBmsqlSub)dBmsqlSub).getState());
                  }
              }
          }
      }

      行結果執:即使執行中,對象的鏈接狀態依然是open,那么用鏈接狀態作為鏈接是否可用的計劃泡湯了,那就只能用自添加的對象來控制鏈接是否可用了。

      Open
      Open
      Open
      Open
      id0
      Open
      Open
      Open
      Open
      Open
      Open

      2.3 測試3:添加對象的狀態,控制鏈接是否可用

      2.3.1 代碼

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      using MySqlConnector;
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
        
      namespace 數據庫鏈接狀態確認
      {
          class Test3:Singleton<Test3>
          {
              public void main() {
                  test1();
                  //test1();
              }
              private void test1() {
                  Task.Factory.StartNew(test2Exe);
                  Task.Factory.StartNew(test2Exe);
                  Task.Factory.StartNew(test2Exe);
              }
              private void test2Exe()
              {
                  int index = 1;
                  while (true)
                  {
                      index++;
                      string sql = "insert into tb1(v1) values(" + index + ")";
                      DBmsql.getMy().ExecuteNonQuery(sql);
                  }
              }
        
              private object MySqlDataReader(DBmsqlSub dBmsqlSub)
              {
                  throw new NotImplementedException();
              }
        
              private void test2State(object dBmsqlSub)
              {
                  while (true)
                  {
                      Console.WriteLine(((DBmsqlSub)dBmsqlSub).getState());
                  }
              }
          }
      }

      2.3.2

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      69
      70
      71
      72
      73
      74
      75
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
        
      namespace 數據庫鏈接狀態確認
      {
          class DBmsql : Singleton<DBmsql>
          {
              List<DBmsqlSub> dBmsqlSubs = new List<DBmsqlSub>();
              /// <summary>
              /// 執行sql命令
              /// </summary>
              /// <param name="CommandText"></param>
              public void ExecuteNonQuery(String CommandText)
              {
                  getDBmsqlSub().ExecuteNonQuery(CommandText);
              }
        
              /// <summary>
              /// 插入數據,并返回插入數據的id
              /// </summary>
              /// <param name="CommandText"></param>
              /// <returns></returns>
              public int insertReturn(string CommandText)
              {
                  int ret = getDBmsqlSub().insertReturn(CommandText);
                  return ret;
              }
              /// <summary>
              /// 執行并返回一個對象
              /// </summary>
              /// <param name="CommandText"></param>
              /// <returns></returns>
              public object ExecuteScalar(string CommandText)
              {
                  object o = getDBmsqlSub().ExecuteScalar(CommandText);
                  return o;
              }
              /// <summary>
              /// 獲取數據處理對象
              /// </summary>
              /// <returns></returns>
              private DBmsqlSub getDBmsqlSub()
              {
                  DBmsqlSub ret = null;
        
                  lock (dBmsqlSubs)
                  {
                      //避免兩個同時取到允許的狀態
                      foreach (DBmsqlSub dBmsqlSub in dBmsqlSubs)
                      {
                          if (!dBmsqlSub.IsUrse())
                          {
                              ret = dBmsqlSub;
                              dBmsqlSub.setIsUser(true);
                              Console.WriteLine("get:" + ret.id);
                              break;
                          }
                      }
                      //避免兩個同時創建對象,產生結果列表的錯誤
                      if (ret == null&& dBmsqlSubs.Count<90)
                      {
                          DBmsqlSub dBmsqlSub = new DBmsqlSub();
                          dBmsqlSubs.Add(dBmsqlSub);
                          dBmsqlSub.setIsUser(true);
                          ret = dBmsqlSub;
                          Console.WriteLine("get:" + ret.id);
                      }
                  }
                  return ret;
              }
          }
      }

      2.3.3

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      69
      70
      71
      72
      73
      74
      75
      76
      77
      78
      79
      80
      81
      82
      83
      84
      85
      86
      87
      88
      89
      90
      91
      92
      93
      94
      95
      96
      97
      98
      99
      100
      101
      102
      103
      104
      105
      106
      107
      108
      109
      110
      111
      112
      113
      114
      115
      116
      117
      118
      119
      120
      121
      122
      123
      124
      125
      126
      127
      128
      129
      130
      131
      132
      133
      134
      135
      136
      137
      138
      139
      140
      141
      142
      143
      144
      145
      146
      147
      148
      149
      150
      151
      152
      153
      154
      155
      156
      157
      158
      159
      160
      161
      162
      163
      164
      165
      166
      167
      168
      169
      170
      171
      172
      173
      174
      175
      176
      177
      178
      179
      180
      181
      182
      183
      184
      185
      using MySqlConnector;
      using System;
      using System.Collections.Generic;
      using System.Data;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
        
      namespace 數據庫鏈接狀態確認
      {
          public class Constand {
              public static string mesConnStr = "server=localhost;port=3306;database=db1;user id=root;password=123456;Charset=utf8;";
          }
          class DBmsqlSub
          {
              static int index = 0;
              public int id = 0;
              private bool isUser = false;
              private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
              MySqlConnection mConn;
              MySqlCommand mCmd;
              public void setIsUser(bool value) {
                  lock (this) {
                      Console.WriteLine("index:" + index + " isuser:" + isUser+" setuser:"+ value);
                      isUser = value;
                  }
              }
        
              public MySqlDataReader MySqlDataReader { get; private set; }
        
              public DBmsqlSub()
              {
                  id = index++;
                  try
                  {
                      mConn = new MySqlConnection(Constand.mesConnStr);
                      mConn.Open();
                      mCmd = new MySqlCommand();
                      mCmd.Connection = mConn;
                  }
                  catch (Exception e)
                  {
                      logger.Error(e.ToString());
                  }
              }
              ~DBmsqlSub()
              {
                  mConn.Close();
              }
              public void Close()
              {
                  mConn.Close();
              }
              public bool isOpen()
              {
                  if (mConn.State == ConnectionState.Closed)
                  {
                      mConn.Open();
                  }
                  if (mConn.State == ConnectionState.Open)
                  {
                      return true;
                  }
                  else
                  {
                      return false;
                  }
              }
              public MySqlCommand getCmd()
              {
                  return mCmd;
              }
              /// <summary>
              /// 如果沒有鏈接,就直接鏈接
              /// </summary>
              private void conn()
              {
                  if (mConn.State != ConnectionState.Open)
                  {
                      mConn.Open();
                  }
              }
        
              /// <summary>
              /// 執行sql命令
              /// </summary>
              /// <param name="CommandText"></param>
              public void ExecuteNonQuery(String CommandText)
              {
                  //setIsUser(true);
                  mCmd.CommandText = CommandText;
                  try
                  {
                      Console.WriteLine("id"+id);
                      mCmd.ExecuteNonQuery();
                  }
                  catch (Exception ex)
                  {
                      logger.Error(ex.ToString());
                  }
                  finally {
                      setIsUser(false);
                  }
              }
        
              /// <summary>
              /// 插入數據,并返回插入數據的id
              /// </summary>
              /// <param name="CommandText"></param>
              /// <returns></returns>
              public int insertReturn(string CommandText)
              {
                  setIsUser(true);
                  int ret = 0;
                  MySqlTransaction sqlTransaction = mConn.BeginTransaction();
                  try
                  {
                      mCmd.CommandText = CommandText;
                      object o = mCmd.ExecuteScalar();
                      sqlTransaction.Commit();
                      ret = int.Parse(o.ToString());
                  }
                  catch (Exception e)
                  {
                      logger.Error(e.ToString());
                      sqlTransaction.Rollback();
                  }
                  finally
                  {
                      setIsUser(false);
                  }
                  return ret;
              }
              /// <summary>
              /// 執行并返回一個對象
              /// </summary>
              /// <param name="CommandText"></param>
              /// <returns></returns>
              public object ExecuteScalar(string CommandText)
              {
                  setIsUser(true);
                  object o = null;
                  mCmd.CommandText = CommandText;
                  try
                  {
                      o = mCmd.ExecuteScalar();
                  }
                  catch (Exception ex)
                  {
                      logger.Error(ex.ToString());
                  }
                  finally
                  {
                      setIsUser(false);
                  }
                  return o;
              }
              public MySqlDataReader ExecuteReader(string CommandText)
              {
                  setIsUser(true);
                  MySqlDataReader mySqlDataReader = null;
                  mCmd.CommandText = CommandText;
                  try
                  {
                      mySqlDataReader  = mCmd.ExecuteReader();
                      //mConn.Close();
                  }
                  catch (Exception ex)
                  {
                      logger.Error(ex.ToString());
                  }
                  finally
                  {
                      setIsUser(false);
                  }
                  return mySqlDataReader;
              }
              public ConnectionState getState() {
                  return mConn.State;
              }
              public bool IsUrse() {
                  return isUser;
              }
          }
      }

      2.3.4

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
        
      namespace 數據庫鏈接狀態確認
      {
          /// <summary>
          /// 單件構象基類
          /// </summary>
          /// <typeparam name="T"></typeparam>
          public class Singleton<T> where T : new()
          {
              static T t = default(T);
              public static T getMy()
              {
                  if (t == null)
                  {
                      t = new T();
                  }
                  return t;
              }
          }
      }

      運行結果:可用看出是幾個鏈接對象在被循環的使用,也基本達到了直接的初衷,用盡可能少的鏈接,完成多線程的調用情景。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      id2
      index:5 isuser:True setuser:False
      index:5 isuser:False setuser:True
      index:5 isuser:True setuser:False
      get:3
      id3
      index:5 isuser:False setuser:True
      get:4
      id4
      index:5 isuser:True setuser:False
      index:5 isuser:False setuser:True
      get:2
      id2
      index:5 isuser:True setuser:False
      index:5 isuser:False setuser:True
      get:3
      id3

      到此這篇關于mysql 數據庫鏈接狀態確認實驗的文章就介紹到這了

      分享到:

      相關信息

      • SQL Server服務器監控

        SQL服務器監控 監控SQL服務器的關鍵指標 內存和CPU利用率 鎖 索引 緩沖區緩存 SQL查詢 復制細節 工作和會議 SQL Server性能監控 使用應用管理器監控SQL Server性能 監視SQL Server總體性能 使用MS SQL server性能監...

        2022-09-11

      • 系統城分享CentOS7下安裝MySQL5.7.39的詳細過程

        CentOS7下安裝MySQL5.7.39的詳細過程,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值...

        2022-09-11

      系統教程欄目

      欄目熱門教程

      人氣教程排行

      站長推薦

      熱門系統下載

      天堂资源中文WWW,久久精品女人天堂AV免费观看,无码专区一ⅤA亚洲V天堂,免费观看在线AⅤ天堂视频