Lock table in PostgreSQL

Question

I have a table "Links" with some download links.

My .NET application reads this table, takes the link, creates a web client and downloads the associated file. I want to create several threads that do this, but each one should read a different record, otherwise two threads are trying to download the same file. How can do this?

I have tried this but it doesn't work:

    public static Boolean Get_NextProcessingVideo(ref Int32 idVideo, ref String youtubeId, ref String title)
    {
        Boolean result = false;

        using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationDB.GetInstance().ConnectionString))
        {
            conn.Open();
            NpgsqlTransaction transaction = conn.BeginTransaction();

            String query = "BEGIN WORK; LOCK TABLE links IN ACCESS EXCLUSIVE MODE; SELECT v.idlink,  v.title " +
                " FROM video v  WHERE v.schedulingflag IS FALSE AND v.errorflag IS FALSE ORDER BY v.idvideo  LIMIT 1; " + 
                " COMMIT WORK;";
            NpgsqlCommand cmd = new NpgsqlCommand(query, conn, transaction);

            NpgsqlDataReader dr = cmd.ExecuteReader();

            if (dr.HasRows)
            {
                dr.Read();


                idVideo = Convert.ToInt32(dr["idvideo"]);
                title = dr["title"].ToString();

                Validate_Scheduling(idVideo); // 
                result = true;
            }

            transaction.Commit();
            conn.Close();

        }
        return result;
    }

Have a look at advisory locks: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Feb 5, 2013 at 13:15
Somewhat of an alternative approach: what if you read the set of pending records into the main thread, then pass the IDs to the new worker threads? Each thread is simply responsible for fetching the details from the DB, doing the work, and then recording the result. You'll have removed any chance of overlap this way and I am guessing the performance is going to be the same since the work done in the thread is going to be the slow part. — Dave S.
– Dave S., Commented Feb 5, 2013 at 15:06

Community · Accepted Answer · 2017-05-23 12:27:03Z

You have a few options here. The one thing you don't want to be doing, as you note, is locking the table.

Advisory locks. The advantage is that these are extra-transactional. The disadvantage is that they are not closed at the transaction and must be closed specifically, and that leakage can eventually cause problems (essentially a shared memory leak on the back-end). Generally speaking I do not like extra-transactional locks like this and while advisory locks are cleared when the db session ends, there are still possible issues with stale locks.
You can have a dedicated thread pull the pending files first, and then delegate specific retrievals to child threads. This is probably the best approach both in terms of db round-trips and simplicity of operation. I would expect that this would perform best of any of the solutions.
You can SELECT FOR UPDATE NOWAIT in a stored procedure which can handle exception handling. See Select unlocked row in Postgresql for an examples.

Collectives™ on Stack Overflow

Lock table in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related