Rails Action Cable实时消息报错：SQLite数据库锁定问题求助

This error pops up because SQLite relies on file-level locking, and your ActionCable broadcast is trying to access the database while the main request's transaction still holds a lock. ActionCable runs in a separate thread, so when you call ActionCable.server.broadcast inside your controller action (which Rails wraps in a transaction by default), the broadcast thread attempts to interact with the database before the original transaction commits—triggering the "database is locked" error.

Here are the most reliable fixes:

1. Move the Broadcast to an after_commit Callback #

The cleanest solution is to trigger the broadcast after the database transaction is fully committed. This ensures the database lock is released before ActionCable tries to access it.

Add this to your Notification model (or whichever model you're creating):

after_commit :broadcast_to_conversation, on: :create

private

def broadcast_to_conversation
  # Use only persisted data here—no uncommitted records
  broadcast_data = {
    content: self.content,
    user_id: self.user_id,
    created_at: self.created_at.iso8601,
    # Include any other needed attributes
  }

  ActionCable.server.broadcast "conversation_#{self.conversation.id}", broadcast_data
end

Then remove the ActionCable.server.broadcast line from your controller. The callback will run automatically once the notification is saved and the transaction is finalized.

2. Pre-Serialize Data to Avoid Database Access During Broadcast #

If you prefer to keep the broadcast in the controller, avoid passing ActiveRecord objects directly to the broadcast. Instead, extract all necessary data into a hash first, so the broadcast thread doesn't need to query the database again:

def create
  @notification = Notification.new(notification_params)

  if @notification.save
    # Pre-build all data needed for the broadcast
    broadcast_data = {
      content: @notification.content,
      user_id: @notification.user_id,
      created_at: @notification.created_at.iso8601,
      username: @notification.user.username # Fetch related data upfront if needed
    }

    # Broadcast the pre-serialized hash
    ActionCable.server.broadcast "conversation_#{@conversation.id}", broadcast_data
    # ... rest of your response logic
  else
    # Handle validation errors
  end
end

This way, the broadcast thread doesn't hit the database at all, eliminating lock conflicts.

3. Increase SQLite's Busy Timeout (Temporary Workaround) #

For a quick fix while implementing the above solutions, you can tell SQLite to wait longer before throwing a locked error. Update your config/database.yml for the SQLite environment:

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000 # Wait 5 seconds instead of the default 1 second

This won't solve the root issue, but it can reduce error frequency while you adjust your code.

Note: SQLite isn't ideal for multi-threaded applications like those using ActionCable. If you plan to scale your app, consider switching to a client-server database like PostgreSQL, which handles concurrent connections far better.

内容的提问来源于stack exchange，提问作者Amer Bearat