CODEWITHSUNDEEP
databaseconcurrency
user1039063
user1039063

Reputation: 211

Locking transactions in databases

I was wondering what happens to a transactions that got blocked by another transaction?

It will best to work through an example, say I have two transactions - T1 and T2 and following scenario:

T1 ........................................................ T2

Lock DB object
Read Q ..................................................Lock Q (T2 is blocked)

Write Q Unlock Q

So does the T2 is un-blocked after T1 is done or is it forever lost? I used to think that T2 was sent into a wait queue and waits there for its turn.

Thank you anyone who would clarify this concept to me :)

Upvotes: 0

Views: 89

Answers (1)

Mat
Mat

Reputation: 206659

There are two common things that happen in this case:

  1. T2 waits until T1 releases the lock. How this is implemented depends on the database software (and potentially the locking primitives provided by the OS).
  2. T2 gets aborted when it tries to lock Q and finds that it is already locked. (Ex. for Oracle, an UPDATE or LOCK TABLE statement issued with the NOWAIT option.)

Having T2 "lost forever" would be a bug in the database engine.

An interesting read about Oracle's locking strategies: How Oracle Locks Data. (That whole chapter, Data Concurrency and Consistency, is interesting if you're studying these database aspects. Note that these details are highly database dependent. What you read there will not apply directly to SQL Server, DB2 or MySQL for instance.)

Upvotes: 1

Related Questions