Database Transactions and Locking – Exact Relationship Explained

lockingtransaction

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response.

As a long-time application developer, I know at some level what a transaction is (I use them all the time). Leaving aside transaction isolation levels for the moment, at a high level a transaction allows a block of work to be completed entirely or not at all, and allows for a certain amount of isolation from other database-modifying activity.

I also know what (in various databases) a lock is, or at least how one behaves (if I lock a table in some way explicitly, then no other process or thread can update anything about that table).

What I am most distinctly not clear about is: in various databases, when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

That is, it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking. Is this transaction-initiated, tranasction-hidden locking the same sort of locking that various databases let me access through constructs such as SELECT FOR UPDATE or explicit LOCK commands? Or are these two concepts completely different?

Again, I apologize for the naïveté of this question; I am happy to be pointed to more foundational sources.

Best Answer

when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would lock a row in a table so that it cannot be locked by another application in the same manner, but your lock would be ignored by the engine itself. These semantics are seldom desired. Most of the time an application locking a row means 'lock it against any means of access/modify'. Side note that locking mechanisms that are strictly application specific do exists, because they are useful. For instance SQL Server has application locks.

it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking.

Locking is one means to achieve this. The major alternative is versioning. Nowadays most databases support both (which also means that if you 'lock' a row in the app but another transaction uses versioning to read the row, it will read it because your locking does not block versioned reads).

You are sort of circling around a concept known in the database implementation world as 'two phase locking protocol'. the linked Wikipedia article is a good starter. If you want to read more detailed explanation about this topic I recommend head to the library and ask for a loan on Transaction Processing: Concepts and Techniques. Pretty much every database out there is, at its core, an implementation of that book.

Related Solutions

Transaction and data consistency during a failure

Bear with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID.

When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction.

The isolation part of ACID is widely misunderstood. There is a degree to which transactions are isolated from each other, as determined by the transaction isolation level. The other elements of ACID are absolute.

Wikipedia states that there are some databases that insure a transaction remains isolated by locking the rows and not unlocking them until the transaction has committed.

This relates to the isolation part of ACID, it doesn't have any impact on your main question.

My question is: How can a database that solely relies on locking guarantee consistency? If a power outage occurs mid-transaction, there may be data partially written to the row.

Your example is not concerned with consistency, it's durability and atomicity. These are guarenteed by write ahead logging (WAL). With WAL, all changes are written to the undo/redo log before they are applied to the data.

In the event of a power failure, a recovery process is run which will read the log to identify a) "mid-flight" transactions that did not commit and b) transaction that did commit but which were not applied to the data. The changes from a) transactions are undone (rolled back), returning the data to its pre-transaction state. The changes from b) are redone (rolled forward), ensuring the data is in the expected post-transaction state.

Even for databases like SQL Server that use a Temporary DB to perform all the transactions, what happens if a power outage occurs as the database is committing the transactions to disk?

TempDB (assuming that's what you're referring to) has absolutely nothing to do with SQL Servers execution of transactions. Are you confusing the role of TempDB in snapshot isolation levels?

Mysql – In InnoDB, does a Transaction imply any implicit locking of a table

In repeatable reads, there is always row-level locking imposed via the gen_clust_index (aka the Clustered Index). This is the beauty of Transactions. What is even more interesting is that InnoDB has four transaction isolation levels, not just one:

There are four values for tx_isolation you can set:

In your particular case, inserting data into TableA actually does not get written to disk. The necessary changes are recorded in three(3) distinct places:

log buffer in memory
in ibdata1
- undo tablespace
- rollback segments
- double write buffer
redo log info in either ib_logfile0 or ib_logfile1

The same applies with the delete in step 5.

Executing a rollback will undo the delete and then undo the inserts.

You must remember something very important: If you want to rollback multiple SQL commands, you must begin like this:

SET autocommit = 0;
START TRANSACTION;

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction (via ROLLBACK) and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

COMMIT;

Give it a Try !!!

When everyone is using repeatable reads

your INSERTs are only seen by you
someone else's DELETEs are only seen by the other person

CAVEAT : Table level locking is never implicit for InnoDB. If you want to lock a table, you must issue, LOCK TABLE explicitly.

Best Answer

Related Solutions

Transaction and data consistency during a failure

Mysql – In InnoDB, does a Transaction imply any implicit locking of a table

Related Question