Why information about lock appears so late, after 5-6 seconds?

Question

I have simple application which have two buttons. First button create transaction and hold it for 30 seconds before commit it.

Second button execute method where I tried to update the same record.

My point is to get information about locked record but exception is throwed after 5-6 seconds.

In PROGRESS, information about lock appears instantly.

How Can I achieve a similar efficiency in C#?

    private void button1_Click(object sender, RoutedEventArgs e)
    {
        Task.Run(  () =>
        {
            using (OdbcConnection con = new OdbcConnection("DSN=myDSN;UID=root;PWD=qweasd"))
            {
                OdbcCommand command = new OdbcCommand();
                OdbcTransaction transaction = null;

                command.Connection = con;

                try
                {
                    con.Open();

                    while (con.State != ConnectionState.Open)
                    {
                        Thread.Sleep(10);
                    }

                    transaction = con.BeginTransaction();

                    command.Connection = con;
                    command.Transaction = transaction;

                    command.CommandText = " UPDATE pub.ad_mstr SET ad_line1 = 'button3' where ad_mstr.ad_addr = 'abcdefgh'  ";

                    command.CommandTimeout = 1;
                    command.ExecuteNonQuery();

                    for (int i = 0; i < 30; i++)
                    {
                        Thread.Sleep(1000);
                    }


                    transaction.Commit();

                }
                catch (Exception exc)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {

                    }
                }

            }
            //_startedTransaction1 = true;
        }); /* Task.Run ( () => */
    }

    private void button2_Click(object sender, RoutedEventArgs e)
    {
        if (!_startedTransaction1)
        {
            Task.Run(() =>
            {
                using (OdbcConnection con = new OdbcConnection("DSN=myDSN;UID=root;PWD=qweasd"))
                {
                    OdbcCommand command = new OdbcCommand();
                    OdbcTransaction transaction = null;

                    command.Connection = con;

                    try
                    {
                        con.Open();
                        while (con.State != ConnectionState.Open)
                        {
                            Thread.Sleep(10);
                        }

                        transaction = con.BeginTransaction();

                        command.Connection = con;
                        command.Transaction = transaction;

                        command.CommandText = " UPDATE pub.ad_mstr SET ad_name = 'button1' where ad_mstr.ad_addr = 'abcdefgh'  ";

                        command.ExecuteNonQuery();

                        transaction.Commit();

                    }
                    catch (Exception exc)
                    {
                        try
                        {
                            transaction.Rollback();
                        }
                        catch
                        {

                        }
                    }

                    //_startedTransaction1 = true;
                }
            });
        }
        else
        {
            //_startedTransaction1 = false;
        }

    }

I want to add that my 'myDSN' is with COMMITED isolation level. Exception which appears after 5-6 seconds is :

"System.Collections.ListDictionaryInternal pgoe1023.dll ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB.ad_mstr."

Do you really want queries to abort immediately when there's a lock on a record? If there's a normal update going on, you'd be throwing errors even if the update took a single millisecond if the other thread just checked at that moment and gave up right away. Normally you'd want deadlocks to resolve immediately, but wait for normal slow queries for at least a few seconds before throwing errors. — Joachim Isaksson
@JoachimIsaksson: I just need to know as fast as possible if my query is locked and then possibly perform some actions. — Wiktor
Wouldn't seem to be settable to below 5 seconds, however I'm no PROGRESS guru by any means so may be missing something. — Joachim Isaksson
What is the problem you're trying to solve? Conflicting locks usually indicates a need to change the design. — Tim Kuehn
@JoachimIsaksson : I think this parameter is for something else. In PROGRESS I can get information about locks immediately and this parameter means how much time need to wait before surrender. Like I said information about record locks appears instantly. — Wiktor

Mike Fechner Mike Fechner · Accepted Answer · 2016-02-16T05:53:39

Looks like you should set the -SQLLockWaitTimeout parameter on your DB server:

http://knowledgebase.progress.com/articles/Article/What-is-the-SQLLockWaitTimeout-Parameter

Why information about lock appears so late, after 5-6 seconds?

1 Answers