SQL Session 會議連線

通訊的會議期間

Connection Object 連接物件

The call to createQueueConnection gets a connection from the connection pool. And there can be a maximum of 10 connections or sessions in each pool. Each connection in the connection pool has its own session pool. This means that there can be 10 session pools that can have a maximum of 10 sessions each.

sessions 和 connections 並非指相同的東西， session 憑藉 connection 物件來操作資料庫，一旦任務完成後， session 會自動將 connection 物件交還给 pool。

    session 利用 connection 行使 transaction 

開發者從 pool 網路連線池拿取命名為 connection 的物件， 可呼叫其仲介軟體或是使用者端軟體中和 SQL 指令同名稱的方法如

    session.commit 
    session.rollback 
    session.close 

將連線物件放回 網路連線池子 pool 裡。

    static void Main(string[] args)
    {
            // 設定相關資料庫連線參數
            string connection = @"Data Source=(LocalDb)\MSSQLLocalDB;Integrated Security=SSPI;AttachDBFilename=D:\Workspace\[03]Test\localdbtest\LocalDbTest\LocalDb.mdf;";

            Enumerable.Range(1, 10).Select(x => Task.Run(() =>
            {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();

                    SqlConnection sqlConnection = new SqlConnection();
                    sqlConnection.ConnectionString = connection;

                    // 開啟連線
                    sqlConnection.Open();

                    sqlConnection.Close();
                    sqlConnection.Dispose();
                    sqlConnection = null;

                    sw.Stop();
                    Console.WriteLine($"連線 {x} 共耗時 {sw.ElapsedMilliseconds} 毫秒");
            })).ToList();

            Console.ReadKey();
    }

Timeout 逾時

然而此時 connection 物件的交易 transaction 並沒有完成與完畢（rollback or commit)。 而不知什么原因（recyle 了，timeout 了），此時 connection 生命週期已盡，仲介軟體則會負責與 DB 重新生成連線，但是由于 transaction 沒有結束，故無法重新連線，也就無法產生新的 Session。

Driver 驅動程式

通常仲介軟體使用驅動物件或是特殊意義的物件產生來連結資料庫：

 object = method_called('agent://admin:password@IP:port/dbname')

Connection Pool 連接池

connection pool

預設上，connection 由 QueuePool 自行管理和重複使用，如要關掉預設，可設定參數值為 NullPool !

關於連接池的相關參數：

• pool_recycle=n

  如果 connection 空閒了 n 秒，自動重新獲取，防止 connection 被 db server 關閉。

• pool_timeout=30

  獲取 session 的超时阈值，

• pool_size=5

  連接數大小，可根據實際情況調整

• max_overflow=10

  超出 pool_size 後可允許的最大連接數，這些連接使用完畢，不放回 pool 中，會被真正關閉的，不復使用。

    #!/usr/bin/env python
    #-*- coding: utf-8 -*-
  
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.pool import NullPool
  
    engine = create_engine('mysql+mysqldb://root:password@127.0.0.1:3306/dbname', poolclass=NullPool) // 將預設設定 false
    Session = sessionmaker(bind=engine)
    session = Session()
    usr_obj_list = session.query(UsrObj).all()
    print usr_obj_list[0].id
    session.close() // 此時可斷開連接池
  

Connection Pool & Session Pool

• Connection Pool

        [ App ]             [Connection Pool]                             [DB]
  
        thread1             virtual connection      
  
                                                  physical_connection_1 _  
  
        thread2             ...       
  
                                                  physical_connection_2 _  
  
        thread3
  
        ...
  

• Session Pool

  • Create, maintain, and manage a pool of stateless sessions transparently.

  • Provide an interface for the application to create a pool and specify the minimum, increment, and maximum number of sessions in the pool.

  • Provide an interface for the user to obtain and release a default or tagged session to the pool. A tagged session is one with certain client-defined properties.

  • Allow the application to dynamically change the number of minimum and maximum number of sessions.

  • Provide a mechanism to always maintain an optimum number of open sessions, by closing sessions that have been idle for a very long time, and creating sessions when required.

  • Allow for session pooling with authentication.

Multi-Threads & Task 多線程和共用資源

Session pooling means that the application creates and maintains a group of stateless sessions to the database.

These sessions are provided to clients as requested. If no sessions are available, a new one may be created. When the client is done with the session, the client releases it to the pool. Thus, the number of sessions in the pool can increase dynamically.

Some of the sessions in the pool may be tagged with certain properties. For instance, a user may request a default session, set certain attributes on it, label it or tag it, and return it to the pool. That user, or some other user, can require a session with the same attributes, and thus request a session with the same tag. There may be several sessions in the pool with the same tag. The tag on a session can be changed or reset.

The behavior of the application when no free sessions are available and the pool has reached its maximum size depends on certain attributes. A new session may be created or an error returned, or the thread may just block and wait for a session to become free.

The main benefit of session pooling is performance. Making a connection to the database is a time-consuming activity, especially when the database is remote. Thus, instead of a client spending time connecting to the server, authenticating its credentials, and then receiving a valid session, it can just pick one from the pool.

實際上有多線程參與同一任務，這些線程之间共享 Session 及其對象；應用程式需要落實的 locking scheme，以便不會『同時訪問 Session 或其狀態』。

解決方法是為每個迸發 thread 維護一個 Session，並將對象從一 Session 複製到另一 Session，通常使用 Session.merge() 方法將對象的狀態复制到本地的新對象中。

flush & commit 預提交和提交

• flush 預先提交，但僅至資料庫內存，並為寫入資料庫文件內

• commit 提交，把內存直接寫入，可以提供查询了