Use lock when more users can write to a .dbf database file?

Question

Sadly, i have to deal with a .dbf file or database if you want, in the server side and i have one question. Since the .dbf is on the server side more users can access it(read and write, i use C# and OdbcConnection). Should i use lock whenever i make insert/update?

---

I will answer to my own question because i want to paste a piece of code. I have a base class for simple operations

void ExecuteNonQuery(string sqlStatement)
T ExecuteScalar<T>(string sqlStatement)
List<T> GetDataTable<T>(string sqlStatement) where T:new()

public class BaseService
{
        protected void ExecuteNonQuery(string sqlStatement)
        {
            using (OdbcConnection odbconn = new OdbcConnection(ConnectionString))
            {
                odbconn.Open();
                OdbcCommand cmd = new OdbcCommand(sqlStatement, odbconn);
                cmd.ExecuteNonQuery();
            }
        }
}

public class UsersService : BaseService
{
        public void SomeInsert()
        {
            string insertUserString = "Insert Into....";
            ExecuteNonQuery(insertUserString);
            return true;
        }
}

I don;t know if it the best solution but those operations were all i need. I am kinda confused how to use lock here.

Answer 1

For me, there is some ambiguity here. When you say lock are you refering to a DB lock or a Thread Locking lock?

If you are reffering to

1. Some kind of DB Lock which locks the file on disk - Yes
2. Thread Locking that depends, if the concurrent access from
   -Threads from the same process then yes a Monitor could do the trick.
   -If the concurrency is from multiple processes on the same box then a Mutex could work.
   -If the concurrency is from multiple separate servers then NO thread locking solutions will not work.

But you are correct, with a file based database with no engine to control concurrent access you will need to deal with concurrent access carefully.

Update-1:

For A simple single server, single application, **no** web garden scenario you could write your data access layer to restrict concurrent access to the DBF files using a lock (syntax sugar for a Monitor). But todays small web application evloves into something that needs to scale up and scale out then your locking solution needs to evolve with it.

You should also be aware that what any solution you go for, it is servely impact how well your application scales with additional load. Event the best serverless databases like Sqlite suffer a significant performance hit when they need to deal with concurrent access from multple threads.

Answer 2

I come from a VFP background, and so am familiar with using DBF files. In reality, when dealing with basic type connections like via ODBC, you only need to apply locks to the data if you are updating more than one table at a time (like using a transaction in a server database), or if you want to be sure that your insert / update won't fail because another user has applied a lock (so your lock attempt fails rather than your data change attempt).

Answer 3

Moved to question