Prevent SQL Database From Lock

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

How we can prevent sql database from locking without usin nolock, readpast and readcommnited.

[Edit: more information from comments]

why is nolock not appropriate in your case?

For Example, I have a table Test. It contains all the information realted to each and every transaction. This table is used by all the employee. Suppose, when i am trying to fetch records from this table and same time some other transaction (Insert, update or delte) is in pogress. Then nolock will not provide exact records from this table. Thats why i am looking for some other way to select records. I have already used nowait, setlocktimeout, nolock, readcommited but it's not giving me correct details.

Why don't you just use IsolationLevel.Serializable and let the locks happen?

Suppose, i have a query and it's taking aroung 70 second to complete a transaction but inmean while other users reqeusted 10-15 request to complete transaction on same table. After 70 sec, all other transction executed succesfully but i got 3-4 deadlock error.

[/Edit //manchanx]

Posted 15-Mar-15 20:21pm

Sumit_Kumar_Sinha

Updated 15-Mar-15 21:37pm

manchanx

v2

Add a Solution

Comments

PIEBALDconsult 16-Mar-15 2:35am

Are you actually having a problem? Or is this an interview or test question?

Sumit_Kumar_Sinha 16-Mar-15 2:42am

It's not interview question. I am trying to fix this issue in my application

[no name] 16-Mar-15 2:48am

Maybe you should give some more background - e.g. why is nolock not appropriate in your case?

Sumit_Kumar_Sinha 16-Mar-15 3:14am

For Example, I have a table Test. It contains all the information realted to each and every transaction. This table is used by all the employee. Suppose, when i am trying to fetch records from this table and same time some other transaction (Insert, update or delte) is in pogress. Then nolock will not provide exact records from this table. Thats why i am looking for some other way to select records. I have already used nowait, setlocktimeout, nolock, readcommited but it's not giving me correct details.

[no name] 16-Mar-15 3:22am

Why don't you just use IsolationLevel.Serializable and let the locks happen?

Sumit_Kumar_Sinha 16-Mar-15 3:29am

Suppose, i have a query and it's taking aroung 70 second to complete a transaction but inmean while other users reqeusted 10-15 request to complete transaction on same table. After 70 sec, all other transction executed succesfully but i got 3-4 deadlock error.

[no name] 16-Mar-15 3:36am

Alright, this is where I leave the boat :-) You should include that kind of information in your question. I'll do that for you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2015-03-15T21:58:00

First of all you have to understand that lock is not an error of SQL...
SQL lives on locks, it is an integral part - a tool that helps SQL keep data integrity...
The only way to not to feel that locks are happening is to do the right design of the system...
for instance a transaction that took 70 seconds sounds totally wrong! It is possible to run a query that long (if it proved to be right) but you have to write it in that way that the lock (and lock will be there) will not took all the time!
For instance to select 17 billion records from a table can take a long time, but will not lock the table, only pages, so will not shut the system down...
To update that much rows on the other hand can lock the hole table, and maybe other tables too...
So, what I'm saying is that there is no a one-for-all solution...You have to see the locking/blocking query and analyze it and then change it...
If you need hep you need to give details...