What's a database transaction?

What's a database transaction?

A database allows multiple users to use it concurrently and hence while a user is making some changes to data in the DB, some other user might be accessing the same data concurrently. This can lead to inconsistent data and hence such a situation should be managed with proper care. Most of the DBMSs use the concept of transactions to handle such a situation, which helps them to maintain data consistency as well as data concurrency.

A transaction is a basically a set of one or more SQL stataments, which will either execute as an atomic unit or will simply rollback and will leave the database as it was prior to the start of execution of the set of SQL statements of the transaction. If all the statements of the transaction gets executed as a stomic unit then the transaction ends with 'commit' statement which makes the changes to the database permanent,otherwise the changes are rolled back using the 'rollback' statement and the database returns to the state it was before the start of the transaction.

The database management system must have the capability to allow access to only one transaction to update a data item at a time otherwise the atomicity of the transaction will be violated and consequently the data will become inconsistent. The DBMSs use the concept of 'locks' to ensure this. A lock is a database object used to ensure that only one transaction gets access to manipulate a data item at a time. Every transaction requires to obtain the lock associated with the data item it intends to change and only after acquiring that lock it can proceed with the changes it needs to make on that data item.

For example: a table lock will not allow that table to be dropped unless an uncommitted transaction on that table gets committed/rolled-back. Similarly a row lock will prevent that row to be modified (or even accessed in required) while another transaction is still modifying that row. The row will become accessible to any other transaction when the transaction accessing it either gets committed or rolled-back.