How do locks and transactions affect database concurrency?

Direct answer

What a strong answer covers

How it works

A transaction takes locks, or in multi-version engines creates versions, to keep its reads and writes consistent until commit. Row locks block other writers on the same rows; some operations escalate to table locks; index and gap locks appear under stricter isolation. The longer a transaction lives and the more rows it touches, the more others wait, and two transactions that acquire the same resources in different orders can deadlock, which the engine resolves by aborting one. Concurrency comes from short transactions that do their reads first and writes last, consistent access order across code paths, indexes that let writes lock few rows, and an isolation level chosen per operation.

Example

A nightly job that updated a million rows in one transaction blocked every order write for ten minutes; splitting it into batches of 5,000 with a commit each removed the blocking. A recurring deadlock between the checkout path, which locked orders then inventory, and the restock job, which locked inventory then orders, disappeared once both acquired inventory first.

Common mistakes

• Holding a transaction open while waiting for user input or an external API.
• Updating rows through an unindexed predicate and locking far more than intended.
• Retrying a deadlocked transaction blindly without fixing the acquisition order.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.