Do I have to lock the database connections when multithreading?

Here is a sample of the class I currently use for database interaction:

using System;
using System.Data;
using System.Collections.Generic;

// Libraries
using log4net;
using log4net.Config;
using MySql.Data.MySqlClient;

namespace AIC
{
    class DB
    {
        private static readonly ILog _logger = LogManager.GetLogger(typeof(DB));
        private MySqlConnection _connection;
        private MySqlCommand _cmd;
        private string _server;
        private string _database;
        private string _username;
        private string _password;

        //Constructor
        public DB(string server, string database, string username, string password)
        {
            log4net.Config.XmlConfigurator.Configure();

            _server = server;
            _database = database;
            _username = username;
            _password = password;

            _connection = new MySqlConnection(string.Format("SERVER={0};DATABASE={1};UID={2};PASSWORD={3};charset=utf8;", _server, _database, _username, _password));
        }

        public bool TestConnection()
        {
            try
            {
                _connection.Open();
                _connection.Close();
                _logger.Info("Connection test, passed...");
                return true;
            }
            catch (MySqlException ex)
         开发者_运维知识库   {
                _logger.Error(ex.ToString());
                return false;
            }
        }

        //open connection to database
        private bool Open()
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                _logger.Info("Starting connection to database...");
                return true;
            }
            catch (MySqlException ex)
            {
                _logger.Error(ex.ToString());
                return false;
            }
        }

        //Close connection
        private bool Close()
        {
            try
            {
                if (_connection.State != ConnectionState.Closed)
                    _connection.Close();
                _logger.Info("Closing connection to database...");
                return true;
            }
            catch (MySqlException ex)
            {
                _logger.Error(ex.ToString());
                return false;
            }
        }

        // Some basic functions
        public bool UserExist(string user)
        {
            string query = "SELECT user_id FROM users WHERE username=@name LIMIT 1";
            if (this.Open())
            {
                try
                {
                    // Assign the connection
                    _cmd = new MySqlCommand(query, _connection);

                    // Prepare to receive params
                    _cmd.Prepare();

                    // Fill up the params
                    _cmd.Parameters.AddWithValue("@name", user);

                    // returned count bool
                    bool result = Convert.ToInt32(_cmd.ExecuteScalar()) > 0;

                    // Close connection
                    this.Close();
                    return result;
                }
                catch (MySqlException ex)
                {
                    _logger.Error(ex.ToString());
                    this.Close();
                    return false;
                }
            }
            else
            {
                _logger.Error("You must be connected to the database before performing this action");
                return false;
            }
        }

        public bool AddUser(string user)
        {
            // .... add user to database
        }

        public bool DelUser(string user)
        {
            // .... del user from database
        }

        public int CountUsers()
        {
            // .... count total users from database
        }
    }
}

Currently, I don't have any management for opening and closing the connections so it will always check wether the database is connected or not, perform the action and close it as shown in the UserExist function.

Considering this, it came to my attention that I might be closing my own connections in the middle or their transactions since I am using this in 2 different threads.

My doubt here is wether this simple class could lock my application for any reason making it unresponsive or cause me any troubles in the long run?

What should I consider, improve, etc.?

Would appreciate code samples.

---

Each thread should have its own connection instance, in your case probably an instance of Db.

But the problem would be solved (a lot) better by not storing a connection in your Db objects at all. The best pattern is to only use connections as local variables in a using() {} statement.

Currently, your class should implement IDisposable (just for the case where your try/catch logic fails).

---

Waiting for exceptions to be thrown and then handle them is not a good way for design a multithreading class. a good design will be with using lock statement. when using a lock you are providing a critical regions so only one thread is allowed to access to the resources at time. once one thread finish its usage the other can proceed and so on.

For example:

so it will always check wether the database is connected or not, perform the action and close

what will happen if two threads try to enter to the same method concurrently? one thread checking if the connection is not set to continue and it finds that the connection is not set so it proceed. but at the middle of its process and before it connects, the Thread Context Switching switches to the other thread and pause the first one, the second thread in turn ask if the connection was set and it will find that it is not, so it connect and proceed. Now the thread context switching switches to the first thread to continue its execution. and the problems begins...

But the scenario is different when using 'lock'; One and only one thread will allowed to access to the method region that marked with the lock. So one thread enter the lock region and establish the connection. at that time the other thread try to access to the method but the first one is still there so the second will be waiting until the first one finishes its work and then it will proceed.

---

You don't have to lock them, but yes: you must ensure 2 threads aren't using the same connection at the same time.

Synchronisation (locks, etc) is one way to do that; isolation is another (better, IMO) way. If two threads never have the same connection then all is good. For this reason, a static connection is never a good idea.