Blockscout
SupportWebsiteGithubDiscord
  • Blockscout Open-Source Explorer
  • 💡About BlockScout
    • Features
      • Ethereum Bytecode Database Microservice
      • Blockscout Redesign
    • Chains Using Blockscout
    • Partners & Integrations
      • RaaS Providers
      • Vera: Verifier Alliance
        • Adding your chain to the Vera database
        • Programmatic verification via the API
    • News & Media
      • Newsletter & Blog
    • Funding
      • L2 Funding Proposal
        • Aux Funding Images
    • Roadmap
  • 🙎Using Blockscout
    • Getting Started
      • Glossary of Terms
      • Main Menu
      • Blocks
      • Transaction Types
      • Beacon Chain Withdrawal Views
    • My Account
      • Watch list
      • Private tags
      • Public tags
      • API keys
      • Custom ABI
      • Verified addresses
        • Copy and Sign Message
      • For developers
    • Dappscout Apps Marketplace
      • DApp Integration
    • Swapscout
      • Earn Merits when using Swapscout
    • Revokescout
    • Autoscout Explorer Launchpad
    • CSV Exports
    • Token Support
      • ERC-1155 Support
    • Merits
      • Streak rewards
      • Activity Pass
      • Leaderboard
      • Badges
      • FAQs
  • 👩‍💻Developer Support
    • For Web3 Developers
    • Link to Blockscout
    • Blockscout APIs
      • Requests & Limits
      • REST API Endpoints
        • Stats API
        • Interpreter API
      • JSON RPC & ETH Compatible RPC Endpoints
        • Account
        • Block
        • Contract
        • Logs
        • Stats
        • Token
        • Transaction
        • ETH RPC API
      • GraphQL in Blockscout
    • Smart Contract Verification
      • Blockscout UI
      • Blockscout smart-contract verification API
      • Hardhat Verification Plugin
        • Sourcify Plugin for Hardhat
      • Foundry Verification
      • Sourcify Verification
      • OpenZeppelin Contract Verification
      • Automate verifications with Catapulta
      • Verification via thirdweb
      • Interacting with Smart Contracts
    • Integrate Merits
    • Chainscout chains list
  • 🏃‍♂️Setup and Run Blockscout
    • General Overview
      • Separate Indexer, Web App, and API
      • Umbrella Project Organization
      • Indexer Architecture Overview
      • ShareLock
      • EVM Version Information
    • Requirements
      • General Backend Requirements / Blockscout Backend Prerequisites
      • Backend/Frontend Compatibility Matrix
      • Hardware & Hosting Requirements
      • Database Storage Requirements
      • Client Setting Requirements
      • Node Tracing / JSON RPC Requirements
      • L2 -> L1 JSON-RPC Method Requests
    • ☑️ENV Variables
      • Backend ENVs: Common
      • Backend ENVs: Chain-Specific
      • Backend ENVs: Integrations
      • Frontend ENVs: Common
        • ENVs
        • Deprecated ENVs
      • Deprecated Backend ENVs
        • Previous ENV Variable Home Page
    • Deployment
      • ⭐Manual Deployment Guide
        • Ubuntu Setup
        • MacOS setup
      • ⭐Docker-compose Deployment
      • ⭐Kubernetes Deployment
      • Rollup Deployment
      • 🌟Cosmos-based chains
      • 🍀Upgrade Guide (v7.0 & v8.0)
      • Frontend Migration
        • All-In-One Container
        • Separate Frontend
        • Customized Backend
        • Proxy Setup
        • FAQs
      • Manual Deployment (backend + old UI)
        • Manual cleaning an instance from the previous deployment
      • Terraform Deployment
      • Ansible Deployment (AWS Cloud)
        • Overview
        • Prerequisites
        • AWS Permissions & Settings
          • Creating a Secret Key Pair
          • Login with AWS CLI
          • Creating an AWS certificate for SSL
          • Manually Cleaning Terraform Related Instances
        • Variables
        • Deploying the Blockscout Infrastructure
        • Deploying Blockscout
        • Destroying Provisioned Infrastructure
        • Common Additional Tasks
        • Common Errors and Questions
        • AWS Marketplace (deprecated)
          • Overview
          • CloudFormation Template
          • Prerequisites & Install Parameters
          • Install from AWS Marketplace
          • AWS EC2 archive node setup with OpenEthereum (formerly Parity)
          • Updating & Redeploying in AWS
          • Customizing CSS
    • Microservices
      • Blockscout ENS (BENS) Name Service Integration
      • Smart Contract Verification
    • Configuration Options
      • Automating Restarts
      • Branding Configs
      • Circle CI Updates
      • Charts and Stats
      • CSS Configuration & Presets
      • Exchange Rates
      • Front-end Config Files
      • haproxy Settings for Blockscout.com
      • Internationalization
      • Logger Configs
      • Memory Usage
      • Metrics
      • My Account Settings
      • Sorting and Pagination
      • Tracing
      • Reown Project ID for contract Read/Write
    • Indexing
      • How do I fix indexer timeouts?
      • How do I update memory consumption to fix indexer memory errors?
    • Testing
    • DB schema
  • FAQs
    • User FAQs
    • Developer FAQs
  • 🧩Resources
    • EaaS: Hosting with Blockscout
    • Contributing to Blockscout
    • Bug Bounty Program
    • Media kit
    • Release Notes
      • v5.3.0: 10/23/23
      • v5.2.0: 6/20/23
      • v5.1.0: 2/13/23
      • v5.0.0: 1/11/23
    • Discord Channel
    • Discussion
    • GitHub Repo
Powered by GitBook
LogoLogo

Privacy and Terms

  • Privacy Notice
  • Terms and Conditions

Copyright © Blockscout Limited 2023-2024

On this page
  • Deadlocks and prevention
  • Imposing the lock acquisition order on a table with Ecto
  • Imposing the lock acquisition order between tables with Ecto
  • Order used for Explorer's tables

Was this helpful?

Export as PDF
  1. Setup and Run Blockscout
  2. General Overview

ShareLock

ShareLock is the row-level locking mechanism used internally by PostgreSQL.

Deadlocks and prevention

When several DB transactions are acting on multiple rows of the same table, it's possible to incur in a deadlock and so into an error. This can be prevented by enforcing the same consistent order of lock acquisition on all the transactions performing INSERT, UPDATE or DELETE on a given table.

On top of this, if multiple DB transactions act on multiple tables a deadlock will occur, even if they follow the order on each table described above, if they acquire locks on said tables in a different order. This can also be prevented by using a consistent order of lock acquisition between different tables.

Imposing the lock acquisition order on a table with Ecto

When INSERTing a list of rows Postgres will respect the order in which they appear in the query, so the reordering can happen beforehand.

For example, this will work:

entries = [...]

ordered_entries = Enum.sort_by(entries, & &1.id)

Repo.insert_all(__MODULE__, ordered_entries)

Performing UPDATEs is trickier because there is no ORDER BY clause. The solution to this is to JOIN on a subquery that SELECTs with the option FOR UPDATE.

Using Ecto this can be done, for example, like this:

query =
  from(
    entry in Entry,
    where: not is_nil(entry.value),
    order_by: entry.id,
    lock: "FOR UPDATE"
  )

Repo.update_all(
  from(e in Entry, join: s in subquery(query), on: e.id == s.id),
  [set: [value: nil]],
  timeout: timeout)

DELETE has the same quirks as UPDATE and it is too solved in the same way.

For example:

query =
  from(
    entry in Entry,
    where: is_nil(entry.value),
    order_by: entry.id,
    lock: "FOR UPDATE"
  )

Repo.delete_all(from(e in Entry, join: s in subquery(query), on: e.id == s.id))

Imposing the lock acquisition order between tables with Ecto

When using an Ecto.Multi to perform INSERT, UPDATE or DELETE on multiple tables the order to keep is between different operation. For example, supposing EntryA was established to be modified before EntryB, this is not correct:

Multi.new()
|> Multi.run(:update_b, fn repo, _ ->
  # operations with ordered locks on `EntryB`
end)
|> Multi.run(:update_a, fn repo, _ ->
  # operations with ordered locks on `EntryA`
end)
|> Repo.transaction()

When possible, the simple solution is to move :update_a to be before :update_b. When not possible, for instance if :update_a depends on the result of :update_b, this can be solved by acquiring the locks in a separate operation.

For example:

Multi.new()
|> Multi.run(:acquire_a, fn repo, _ ->
  # acquire locks in order on `EntryA`
end)
|> Multi.run(:update_b, fn repo, _ ->
  # operations with ordered locks on `EntryB`
end)
|> Multi.run(:update_a, fn repo, %{acquire_a: values} ->
  # operations (no need to enforce order again) on `EntryA`
end)
|> Repo.transaction()

Note also that for the same reasons multiple operations on the same table in the same transaction are not safe to perform if they each acquire locks in order, because locks are not released until the transaction is committed.

Order used for Explorer's tables

This is a complete list of the ordering currently in use on each table. It also specifies the order between tables in the same transaction: locks for a table on top need to be acquired before those from a table on the bottom.

Note that this should always be enforced because as long as there is one DB transaction performing in a different order there is the possibility of a deadlock.

schema module
table name
ordered by

Explorer.Chain.Address

addresses

[asc: :hash]

Explorer.Chain.Address.Name

address_names

[asc: :address_hash, asc: :name]

Explorer.Chain.Address.CoinBalance

address_coin_balances

[asc: :address_hash, asc: :block_number]

Explorer.Chain.Block

blocks

[asc: :hash]

Explorer.Chain.Block.SecondDegreeRelation

block_second_degree_relations

[asc: :nephew_hash, asc: :uncle_hash]

Explorer.Chain.Block.Reward

block_rewards

[asc: :address_hash, asc: :address_type, asc: :block_hash]

Explorer.Chain.Block.EmissionReward

emission_rewards

[asc: :block_range]

Explorer.Chain.Transaction

transactions

[asc: :hash]

Explorer.Chain.Transaction.Fork

transaction_forks

[asc: :uncle_hash, asc: :index]

Explorer.Chain.Log

logs

[asc: :transaction_hash, asc: :index]

Explorer.Chain.InternalTransaction

internal_transactions

[asc: :transaction_hash, asc: :index]

Explorer.Chain.Token

tokens

[asc: :contract_address_hash]

Explorer.Chain.TokenTransfer

token_transfers

[asc: :transaction_hash, asc: :log_index]

Explorer.Chain.TransactionAction

transaction_actions

[asc: :hash, asc: :log_index]

Explorer.Chain.PolygonEdge.Deposit

polygon_edge_deposits

[asc: :msg_id]

Explorer.Chain.PolygonEdge.DepositExecute

polygon_edge_deposit_executes

[asc: :msg_id]

Explorer.Chain.PolygonEdge.Withdrawal

polygon_edge_withdrawals

[asc: :msg_id]

Explorer.Chain.PolygonEdge.WithdrawalExit

polygon_edge_withdrawal_exits

[asc: :msg_id]

Explorer.Chain.Optimism.OutputRoot

op_output_roots

[asc: :l2_output_index]

Explorer.Chain.Optimism.TxnBatch

op_transaction_batches

[asc: :l2_block_number]

Explorer.Chain.Optimism.Deposit

op_deposits

[asc: :l2_transaction_hash]

Explorer.Chain.Optimism.DisputeGame

op_dispute_games

[asc: :index]

Explorer.Chain.Optimism.FrameSequence

op_frame_sequences

[asc: :id]

Explorer.Chain.Optimism.FrameSequenceBlob

op_frame_sequence_blobs

[asc: :id]

Explorer.Chain.Optimism.WithdrawalEvent

op_withdrawal_events

[asc: :withdrawal_hash, asc: :l1_event_type]

Explorer.Chain.Optimism.Withdrawal

op_withdrawals

[asc: :msg_nonce]

Explorer.Chain.Optimism.EIP1559ConfigUpdate

op_eip1559_config_updates

[asc: :l2_block_number]

Explorer.Chain.Optimism.InteropMessage

op_interop_messages

[asc: :nonce, asc: :init_chain_id]

Explorer.Chain.Address.TokenBalance

address_token_balances

[asc: :address_hash, asc: :token_contract_address_hash, asc: :block_number]

Explorer.Chain.Address.CurrentTokenBalance

address_current_token_balances

[asc: :address_hash, asc: :token_contract_address_hash]

Explorer.Chain.Scroll.Batch

scroll_batches

[asc: number]

Explorer.Chain.Scroll.BatchBundle

scroll_batch_bundles

[asc: final_batch_number]

Explorer.Chain.Scroll.Bridge

scroll_bridge

[asc: :type, asc: message_hash]

Explorer.Chain.Scroll.L1FeeParam

scroll_l1_fee_params

[asc: :block_number, asc: tx_index, asc: name]

Explorer.Chain.Shibarium.Bridge

shibarium_bridge

[asc: :operation_hash, asc: l1_transaction_hash, asc: l2_transaction_hash]

Explorer.Chain.StakingPool

staking_pools

[asc: :staking_address_hash]

Explorer.Chain.StakingPoolsDelegator

staking_pools_delegators

[asc: :delegator_address_hash, asc: :pool_address_hash]

Explorer.Chain.ContractMethod

contract_methods

[asc: :identified, asc: :abi]

Explorer.Market.MarketHistory

market_history

[asc: :date]

Explorer.Chain.Withdrawal

withdrawals

[asc: :index]

Explorer.Chain.Zkevm.TransactionBatch

zkevm_transaction_batches

[asc: :number]

Explorer.Chain.Zkevm.BatchTransaction

zkevm_batch_l2_transactions

[asc: :hash]

Explorer.Chain.Zkevm.LifecycleTransaction

zkevm_lifecycle_l1_transactions

[asc: :id]

Explorer.Chain.Zkevm.Bridge

zkevm_bridge

[asc: :type, asc: :index]

Explorer.Chain.Zkevm.BridgeL1Token

zkevm_bridge_l1_tokens

[asc: :address]

Explorer.Chain.ZkSync.TransactionBatch

zksync_transaction_batches

[asc: :number]

Explorer.Chain.ZkSync.BatchBlock

zksync_batch_blocks

[asc: :hash]

Explorer.Chain.ZkSync.BatchTransaction

zksync_batch_transactions

[asc: :hash]

Explorer.Chain.ZkSync.LifecycleTransaction

zksync_lifecycle_transactions

[asc: :id]

Explorer.Chain.Celo.EpochReward

celo_epoch_rewards

[asc: :block_hash]

Explorer.Chain.Celo.PendingEpochBlockOperation

celo_pending_epoch_block_operations

[asc: :block_hash]

Explorer.Chain.Celo.ValidatorGroupVote

celo_epoch_validator_group_votes

[asc: :transaction_hash, asc: :account_address_hash, asc: :group_address_hash]

Explorer.Chain.Celo.ElectionReward

celo_election_rewards

[asc: :block_hash, asc: type, asc: :account_address_hash, asc: :associated_account_address_hash]

Explorer.Chain.Zilliqa.QuorumCertificate

zilliqa_quorum_certificates

[asc: :block_hash]

Explorer.Chain.Zilliqa.AggregateQuorumCertificate

zilliqa_aggregate_quorum_certificates

[asc: :block_hash]

Explorer.Chain.Zilliqa.NestedQuorumCertificate

zilliqa_nested_quorum_certificates

[asc: :block_hash, asc: proposed_by_validator_index]

Explorer.Chain.Filecoin.PendingAddressOperation

filecoin_pending_address_operations

[asc: :address_hash]

Last updated 1 month ago

Was this helpful?

🏃‍♂️