none
Row Locks in SQL Table

    Pregunta

  • Hi,

    I've a SQL table containing 300,000+ records in that. I've one Timer Job updating that entire table daily and there is one control which updates single row of the table.

    When the Timer job is updating, if the controls is trying to update any row, Table is getting suspended and throwing Timeout exception on the control. I understood the job is locking rows and its not allowing other update to execute.

    I need to know the better approach to handle this. Please let me know if you need any other information to understand this.

    Thanks,

    Vikram

    martes, 10 de abril de 2012 7:28

Todas las respuestas

  • Hi,

    Please, could you tell us how is working your timer job updating the entire table ( code would be appreciated to help you ) ?

    Idem for your "control trying to update any row" ? ( code also appreciated )

    If you are using an application, i suppose that you are using a Command ( SqlCommand,OdbcCommand,OledbCommand ). This Command has a timeout which is set to 30 seconds

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx 

    a simple way would be to do test how long time the table is locked by your "timer job" and to set the CommandTimeout to a value greater than the mean time during which the table is locked.

    Another way would be more difficult  to code , but maybe more efficient but it is depending of the way the timer job is started. It consists to check whether the timer job is running. In your application trying to update a row, you do a loop until the timer job is no longer executing.

    You can use a SELECT on the sys.dm_exec_sessions http://msdn.microsoft.com/en-us/library/ms176013(SQL.100).aspx ( usually i am relying on the program_name property which is usually set by the ApplicationName of the connection string .

    A last question : how your timer job is started ? How is it scheduled . Is it a job using SSIS services ?

    I am sorry , but with so few informations , it is difficult to help you more efficiently. It is why we are waiting for your feedback to propose a possible solution.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    • Propuesto como respuesta Peja Tao miércoles, 11 de abril de 2012 2:40
    martes, 10 de abril de 2012 8:21
  • Hello Papy,

    Thanks for the response.

    I've a timer job running in SharePoint 2010 daily in the morning to update one SQL table. We are not using SSIS. Below is the code being called inside the job over 200K times.

    using (SqlConnection con = new SqlConnection(connString))
                {
                    con.Open();
                    try
                    {
                        string text = String.Format(
                            "UPDATE Table1 SET Table1Title='{1}' WHERE Table1Url='{0}'\r\n",
                            url.Replace("'", "''"),
                            title.Replace("'", "''")
                            );

                        SqlCommand cmd = new SqlCommand(text, con);
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandTimeout = 60;
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        con.Close();
                    }
                }

    Then, I've a web part in the application which updates the same table with a stored Procedure.

    IF EXISTS(SELECT SourceURL FROM Table1 with (NOLOCK) WHERE SourceURL=@SourceURL AND Table1URL=@Table1URL and BusinessUnit = @Table1Unit and Table1ID =@Table1ID)
    begin
    set nocount on
    UPDATE Table1 SET Hits=Hits+1, Table1Title=@Table1Title, Table1Unit = @Table1Unit, Table1ID =@Table1ID WHERE SourceURL=@SourceURL AND Table1URL=@Table1URL and Table1Unit = @Table1Unit and Table1ID =@Table1ID
    end
    ELSE
    begin
    INSERT INTO Table1 VALUES(@SourceURL, @Table1URL, @Table1Title,1, @Table1Unit, @Table1ID)
    end

    I'll go through the links and see if I can fix with the information you've provided. Please let me know if you need any more information.

    Thanks,

    Vikram

    miércoles, 11 de abril de 2012 1:09
  • hi plz refer the below link, it may help you.

    http://www.sqlteam.com/article/row-locking

    martes, 17 de abril de 2012 12:30