亚洲精品中文字幕无码专区一,中文字幕av一区乱码深夜动态福利,粗大的内捧猛烈进出少妇

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

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

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

          時間:2022-10-02來源:www.snowmanhill.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

          系統教程欄目

          欄目熱門教程

          人氣教程排行

          站長推薦

          熱門系統下載

          亚洲精品中文字幕无码专区一,中文字幕av一区乱码深夜动态福利,粗大的内捧猛烈进出少妇