views:

17

answers:

0

I'm working with a single NpgsqlConnection from 2 threads. Connection is protected via lock. All SQL commands are in fact calls to 3 different stored procedures, one procedure per call.

The program runs whole day, 24 hours. At some point (not very often - like, several times a day) when another call is issued to one of these stored procedures (always the same one, and it simply updates or inserts a record into a small table), DataAdapter.fill(DataTable) hangs. Totally and completely. Which results in a deadlock.

It's not PostgresSQL - it doesn't lock the row, when deadlock occurs I can still call this stored procedure from PgAdmin and everything will be ok.

I'm at a loss here completely. The worst part is that bug repeats from time to time, I don't understand what are the conditions which cause it. Err... help?

My code (simplified a bit, the part where it hangs is marked with "<-- hangs here"):

DataTable ExecuteSql(strin sql) 
{
    DataTable res;
    if (Monitor.TryEnter(locker, 1000)) 
    {
        try { 
            DataTable tbl = new DataTable();
            adapter.SelectCommand.CommandText = sql;
            adapter.fill(tbl);        // <-- hangs here
            return tbl;
        } 
        finally { Monitor.Exit(locker); }
    }
    else { 
        throw new TimeoutException("Lock timeout. Possible deadlock");
    }
}