Support locking tables

[huuhait]

Hi how i can lock table?

[jwoertink]

Hey @huuhait! Right now Avram doesn't have any built-in support for locking tables directly. However, you can always drop down to raw SQL to do things like this.

I haven't used locks, but I believe they only persist during a transaction, right? In which case, you can use:

AppDatabase.transaction do
  AppDatabase.run do |db|
    # db is an instance of the crystal-db https://github.com/crystal-lang/crystal-db
    # which will just proxy directly to the pg shard https://github.com/will/crystal-pg
    db.query "CUSTOM SQL"
  end
end

Then if your lock needs to do a select on a specific model, you can build the query like normal UserQuery.new.admin(true), but call to_prepared_sql on it allowing you to build out your custom lock SQL using the query from a model.

sql = <<-SQL
BEGIN WORK;
LOCK TABLE #{table_for(User)} IN SHARE MODE;
#{UserQuery.new.admin(true).to_prepared_sql};
INSERT INTO admin_users VALUES (_id_);
COMMIT WORK;
SQL

db.query(sql)

Here's some more guides on using the raw SQL options with Avram. https://luckyframework.org/guides/database/raw-sql

If you get this working all buttery smooth, it would be great if you could open an issue on the website with how you did this. Then we could add some documentation showing how to handle it for others!

[huuhait]

Hey @huuhait! Right now Avram doesn't have any built-in support for locking tables directly. However, you can always drop down to raw SQL to do things like this.

I haven't used locks, but I believe they only persist during a transaction, right? In which case, you can use:

AppDatabase.transaction do
  AppDatabase.run do |db|
    # db is an instance of the crystal-db https://github.com/crystal-lang/crystal-db
    # which will just proxy directly to the pg shard https://github.com/will/crystal-pg
    db.query "CUSTOM SQL"
  end
end

Then if your lock needs to do a select on a specific model, you can build the query like normal UserQuery.new.admin(true), but call to_prepared_sql on it allowing you to build out your custom lock SQL using the query from a model.

sql = <<-SQL
BEGIN WORK;
LOCK TABLE #{table_for(User)} IN SHARE MODE;
#{UserQuery.new.admin(true).to_prepared_sql};
INSERT INTO admin_users VALUES (_id_);
COMMIT WORK;
SQL

db.query(sql)

Here's some more guides on using the raw SQL options with Avram. https://luckyframework.org/guides/database/raw-sql

If you get this working all buttery smooth, it would be great if you could open an issue on the website with how you did this. Then we could add some documentation showing how to handle it for others!

is Avram will support locking table in futures? i think it is necessary for stable big web/app

[jwoertink]

We probably can. I haven't seen anyone request that. I've worked on some really large apps, and have never needed to use table locking, so I'm not really sure how it all works. If we can figure out a clean type-safe way to add it in, then I think it would be a welcomed change!

[jwoertink]

I'll leave the issue open. If you or anyone else comes across this and has a really good understanding of how to use them, please add some notes in here and we can work out how to best integrate this in to Avram.

[huuhait]

We probably can. I haven't seen anyone request that. I've worked on some really large apps, and have never needed to use table locking, so I'm not really sure how it all works. If we can figure out a clean type-safe way to add it in, then I think it would be a welcomed change!

i'm building a cryptocurrency exchange so i need lock table for orders, their are extremely related, they may create errors if dont have locking table .-.

[jwoertink]

That's awesome @huuhait! Definitely another area I don't know a single thing about. Once you have it working, any tips you can share here on how you did it would be awesome.

[huuhait]

what do you need me to provide information? i'll describe if possible

about how it work or why we need it in my project?

[cyangle]

Rails active_record supports two types of locking: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

[jwoertink]

Thanks for that info @cyangle.

@huuhait did you happen to get table locking working in a branch, or in your app? If not, that's ok. I was just curious if work on this had been started already or not. 😄

[BrucePerens]

I did have a need for table or column locking today. I am creating records with a unique key constraint. I check for existence of the key with a query, and then if it doesn't already exist, I create it. But there's a race: another instance can create the key in the interval between when I check for its existence and then create it. And then creating the record raises #<PQ::PQError:duplicate key value violates unique constraint key_name> and I must back out of all transactions before Postgres will take another command. I have to use table or column locking to handle this more gracefully.

[jwoertink]

@BrucePerens does that happen when using the upsert method? I've never needed to use row or table locking before, but I know we want to add that in. I think it's just a matter of coming up with that API. For example, would this be something you'd always use a specific operation for? Or is this always some sort of one-off type deal?

class SaveUser < User::SaveOperation
  lock_table
end

SaveUser.upsert(...)

Would this be a viable solution, or does it not make sense? This is where it gets foggy for me. I'd really love to see how others are using this in their apps to better determine what we would need to do to make it native.

[BrucePerens]

The table in question is for my url_path object, which is the fast resolution from a URL path to one of my other records. If I use upsert to create a new record which holds a reference to a new url_path, and there is a collision, we are left with something inconsistent. Probably two records that both hold references to a url_path that should have only one owner. Most likely this is a database consistency error because the reference should be unique.

I could upsert both objects, but what I really want to do is detect the conflict and see which has better data before replacing one with the other.

[michaeltelford]

I managed to get table locking working within the context of a SaveOperation. Basically I have a before_save :lock_table which does:

AppDatabase.exec "BEGIN;"
AppDatabase.exec "LOCK TABLE ..."

Then the SaveOperation does its BEGIN (via database.transaction) and then the usual INSERT and COMMIT. Since the commit is what unlocks the table again, I don't need any custom SQL for this. The only superfluous aspect of this approach is that there is two BEGIN's being executed (which isn't an issue, it just generates a Postgres warning).

Ideally I could do something like before_begin for the current before_save tasks and then do a before_save (which is now after database.transaction) and then I could remove my superfluous BEGIN statement.

So the long story short is that it works, but just not as idiomatically as you'd like. Maybe in the future a new hook (before_begin etc) would make sense for locking tables and other similar use cases that need to be run after the BEGIN statement.

[BrucePerens]

Michael's method is interesting, but I think I need to lock the table at transaction begin, not the beginning of the save. The usual operation would be to read from the table, write something based on what I have read, commit (with implied unlock). I'm unfamiliar with the necessary SQL, I'll have to try it sometime.

[BrucePerens]

I don't know if this actually works, but here is my table locker:

class AppDatabase
  # Lock one or more tables. Creates a transaction, executes the block within the 
  # transaction with the given tables locked.
  #
  # Unlocking will happen when the *outermost* transaction commits.
  #
  # #Arguments#
  # * *mode*: The locking mode to use, as an SQL fragment in a `String`. The default
  #   is `"EXCLUSIVE"`.
  # * The other arguments are symbols or strings containing the names of the tables
  #   to lock.
  def self.lock_table(*positional_arguments, mode : String = "EXCLUSIVE")
    tables = String.build do |s|
      first = true
      positional_arguments.each do |a|
        s << ", " if !first
        s << a.to_s
        first = false
      end
    end
    self.transaction do
      self.exec "LOCK TABLE #{tables} IN #{mode} MODE;"
      yield
    end
  end
end

[BrucePerens]

Here is how you select with row locking FOR UPDATE in avram:

  sql = <<-END
    SELECT * from product_categories
    WHERE site_id = $1 AND name = $2
    FOR UPDATE OF product_categories;
  END
  node = AppDatabase.query_all(sql, as: ProductCategory, args:[ctx.site.id, "Goods"]).first?

Postgres seems to do some locking without being told, as part of regular operations, look at https://www.postgresql.org/docs/9.1/explicit-locking.html for a hint.