22 November 2013

Database Session, Transaction and Locks

Database Session:-
Session literally means "a single continuous sitting". If a client successfully authenticates to the database a successful connection will be established to the database. A successful connection to a database creates a session in the database and when the connection gets closed the session gets ended. what ever the client does using that connection is part of the session. I mean what ever the queries that the client executes using that connection and what all the temporary tables that get created for executing those quires  are part of the session. Until the user does the commit all the modification that he does using the connection are part of the session. If he does the commit, all the temporary modification that are part of the session goes permanent or if he closes the connection without a commit all the modification that he did using that connection will be rolled back and the session will be removed. The ultimate point is "one connection is one Session" in the database.

Transaction in Database:-
A transaction is a unit of work that you want to treat as "a whole". It has to either happen in full, or not at all. There is no intermediate state. Also can be called Atomic operation. One commit is one transaction in database.

Locks:-
The locks that you attain in a transaction are only part of that transaction. For example if you attain locks on some rows using "Select for update" the  locks retain only until the commit happens. If the commit just happen after the query execution then the locks are released so if you have to use "select for update" which attains locks on some rows then before you execute the query just make the auto commit false and then execute the query so the locks are attained and then commit the updated data back so the data is updated and the locks get released.  

No comments: