Record lock, Table Lock, blocking accessing the table

Whenever a table being updated (before commit), I can't access the table from ASP page.. Below is the code accessing the table.

public DataSet GetRollID(string UserName)
    {
        FbConnection objCon = new FbConnection(csb.ToString());
        objCon.Open();
        DataSet dsRollID = new DataSet();
        FbDataAdapter da = new FbDataAdapter("SELECT * FROM USERDETAILS WHERE USERNAME='" + UserName)+ "' AND ACTIVEFLAG='ACTIVE'", objCon);
        da.Fill(dsRollID);        
        objCon.Close();
        return dsRollID;
    }

Using asp.net 2.0 with Firebird.

Looks like the lock is at table level.. Hot to overcome this issue?

Thanx
pcssecureAsked:
Who is Participating?
 
dougaugCommented:
First, you should evaluate why the update is locking your table. See if you have appropriate indexes on your table to avoid table scan and unnecessary locks (on pages or records), mainly if you are updating a lot of records.

Could you post your update code and the table structure here, please?
0
 
Nick UpsonPrincipal Operations EngineerCommented:
firebird doesn't have a table level lock

select * is a bad idea generally, as any change to the table will cause it to fail

if a row is being updated (commit pending) the select should retrieve the last value visible to it
0
 
pcssecureAuthor Commented:
Amazing, Creating index solved the problem.

Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.