Codeigniter SQL Server连接是否会一直存在？ 可以从Codeigniter for MSSQL Connection连接非持久性

Question

Why SQL Server @@spid is same for all session when connecting from Codeigniter 3.0 PHP ?

I have successfully connected to SQL Server from Codeigniter 3.0.2. I am using the sqlserv Driver. Why is the SQL Server @@spid always returning the same for different user sessions.
My database connection is not set to Persist. How can i make it not persist. Here is my DB connection.

$db['default'] = array(
    'dsn'   => '',
    'hostname' => '127.0.0.1',
    'username' => 'user',
    'password' => 'password',
    'database' => 'Testdb',
    'dbdriver' => 'sqlsrv',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

I tried the same for Mysql Connection from codeigniter. It works non persistent as expected. ?

Answer 1

In reference to the below link, https://msdn.microsoft.com/en-us/library/cc644930%28SQL.90%29.aspx

Connection Pooling (Microsoft Drivers for PHP for SQL Server)

The following are important points to note about connection pooling in the Microsoft Drivers for PHP for SQL Server:

The Microsoft Drivers for PHP for SQL Server uses ODBC connection pooling.

By default, connection pooling is enabled. When you connect to a server, the driver attempts to use a pooled connection before it creates a new one. If an equivalent connection is not found in the pool, a new connection is created and added to the pool. The driver determines whether connections are equivalent based on a comparison of connection strings.

When a connection from the pool is used, the connection state is reset.

Closing the connection returns the connection to the pool.

I could resolve the problem by setting

$db['default'] = array(
    'dsn'   => '',
    'hostname' => '127.0.0.1',
    'username' => 'user',
    'password' => 'password',
    'database' => 'Testdb',
    'dbdriver' => 'sqlsrv',
    'dbprefix' => '',
    'pconnect' => TRUE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

when i set pconnect' => TRUE , 'app_id'=>''.rand() during connection , new @@SPIDs are created.

So by effectively setting the 'app_id' , it is possible to control the pooling for application or by users groups or by users

caution:By setting pconnect' => TRUE , 'app_id'=>''.rand() , you are effectively nullifying the effect of connection pooling in your application. A careful use is recommended.