Related to transaction in dbms

Here you go, follow these links and study how they work—which as you mentioned, you already are aware of.

database - Correct use of transactions in SQL Server 2008 - Stack Overflow[^]
BEGIN TRANSACTION (Transact-SQL) - SQL Server | Microsoft Docs[^]

Now, as you can see that a TRANSACTION query exposes other clauses and SQL commands that you can use to control how a transaction is processed. COMMIT and ROLLBACK allow you to control the behavior as well as side effects of the queries that are executing and those that have been executed. Let me give you 2 scenarios that can explain the transactions.

First of all, suppose that you have a system where at any given time at least more than 5 or so users are connected and working—5 is just arbitrary, it can be just 2 users that can mess up with the consistency of the data in the system. Now, you try to execute a query, say, seat reservation—a very old school problem in computer science—but since multiple users are trying to book the tickets, there might be a chance that user 3 was provided with 7 seats and he tried to reserve 4 (3 left?), but before user 3 could finish the order, user 5 booked 4 tickets (at random!) and user 3 also(!) submitted the request for 4 tickets... Who gets the tickets and how many? And how many tickets are left for other users... These problems are similar to race conditions in multithreaded environments. You can try this out using TRANSACTION and verify that only the user who tried to book seats first gets them, and other users wait for a command. Yes, they wait. They don't hold a value of tickets they are looking for.

Secondly, in the light of what I have just spoken above, how should the database system proceed if it finds that the response for a value is now less than what is expected? For example, user wants 4 seats but the result is only 3 seats—and supposedly we have already made a change in one of the tables as we went through the transaction—the system should do what? A transaction will let you control the flow. If everything goes fine,

1. You query the tickets table and find that tickets are available.
2. You update the status of the tickets in ticket table for sold.
3. You query the user account and find they don't happen to have enough credit.

Note that above cases can be either way (first credit check and then ticket check, perhaps) and in these cases you use ROLLBACK to undo all the changes, or you COMMIT the changes to persist them in the system.

You cannot do these and maintain that ACID property of your database without using some external services or languages, like C# or Java support. You can say that you can only query for all the information and after every constraint is met you can process it, but that doesn't guarantee the locking that is needed, and in case someone else has requested to purchase the tickets while you are holding previous information, that is what we are trying to ignore from here.

Same concepts are supported by other relational databases, such as Oracle, MySQL, etc. You should check their documentation to see which one applies to your database system.