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.