INTERVIEW QUESTIONS MICROSOFT DOTNET DETAILS

Question: When multiple users are working on one application, the SQL/Oracle statement gets locked when one user is working, thus another user cannot work simultaneously. How can multiple users work. Thus give me syntax for freeing SQL/Oracle queries in order for working of multiple users.

Answer: Oracle statement does not get locked. What gets locked is the 'row' alone. Normally, single row needs to be modified by a single transaction only at a time, and oracle correctly locks the row, when it updates/deletes. For reads, i.e, 'select' statement, the row is not locked unless it is a 'select for update'; The complete table never gets locked except in case of some DCL statements, when it is required to be locked.

Oracle statement does not get locked. What gets locked is the 'row' alone. Normally, single row needs to be modified by a single transaction only at a time, and oracle correctly locks the row, when it updates/deletes. For reads, i.e, 'select' statement, the row is not locked unless it is a 'select for update'; The complete table never gets locked except in case of some DCL statements, when it is required to be locked. Source: CoolInterview.com

you can use nolock statement in the select query. This will not lock the rows or records which is used by another user.

Ex: select columnlist from tablename (nolock) Source: CoolInterview.com

Perform every insert, update and delete query via stored procedure, because a stored procedure executes as a whole and performing locking itself. Please try this, thanks. Source: CoolInterview.com

You can use No Lock in select statement so that multiple user can work on it.

Syntex for SQL Server:

SELECT * FROM Employees(NoLock) Source: CoolInterview.com

This can be done by using a transaction and setting the isolation level according (ReadCommitted, ReadUncommitted, RepeatableRead or Synchronous). A new transaction shud be created for different users to maintaint he integrity of the data. Source: CoolInterview.com

When someone begins modifying data in SQL Server, that data becomes locked. Locking prevents any other connection from accessing that data-even to query it. The data will only be accessible to other users when the transaction is either committed or rolled back.

I can demonstrate this behavior rather simply using the pubs sample database that comes with SQL Server. Open up two windows in Query Analyzer. In the first window, raise the price of all books in the pubs database by executing the following SQL:

use pubs
go
begin tran
update titles
set price = price * 1.05
where

title_id = 'BU2075'
Because I haven't yet executed a commit statement in the code the change is still not finished.
Now in the other window, I'll try to query the titles table by executing:

select title_id,title,price
from titles
order by title_id.
You will not get any results. Instead, the little globe on the bottom will keep spinning. Although I'm only updating one row, my select statement includes the one row that is being changed. Therefore, no data will be returned till the update is either committed or rolled back.
The solution chosen by SQL Server potentially lowers throughput and performance. The longer data is locked, or the larger the amount of data being locked, the more likely that other users will have to wait for their statements to execute. Therefore, as a programmer, it is important to keep transactions small and fast.

In more recent versions of the product, Microsoft has enabled SQL Server to lock smaller amounts of data at a time, which is a big improvement. In version 6.5 and below, the smallest lock was on a page. So even if you were only modifying one row out of 10 on a page, all 10 rows would be locked. This increased the likelihood of causing a wait for another connection till the modification was completed. In version 7, Microsoft introduced row-level locking, so now SQL Server locks only the rows that are actually being changed.

SQL Server's solution sounds simple, but there's a lot that has to happen behind the scenes to maintain sufficient performance. For example, if you are modifying many rows, SQL Server will escalate the lock to the page level or even the table level so as not to have to track and maintain separate locks for each record.
Source: CoolInterview.com

In this situation data base relase the lock after some time which is in millisecond.There for if transaction is fail by any user, he has to handle it explicitly by retry it again.We should aviod to handle the lock explicitly, because every data base handle lock by itself. Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question