Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL concurrency

Posted on 2004-08-02
6
Medium Priority
?
693 Views
Last Modified: 2008-03-10
Hello, Experts.

I am led to believe that SQL Server (version 7.0 and probably all versions) automatically handles concurrency.  Is this correct?  Can somebody elaborate on this please?

Thanks
0
Comment
Question by:sublimation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11692859
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11692953
Answer to original question is Yes. With potential for problems as dirty cache is an issue
0
 
LVL 34

Expert Comment

by:arbert
ID: 11694445
I guess I would expand a little bit.  The answer is YES to a certain extent.  If you have a bad or poorly written application, you can make it so concurrency is impossible--SQL will do what it can to maintain concurrency....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
grant300 earned 360 total points
ID: 11694851
All serious (engine-based) relational databases (SYBASE, Oracle, DB2, Ingres, Informix, SQL Anywhere, MySQL, PostgreSQL, Interbase, etc.) handle concurrency.  MS Access and some of the other file-based databases are particularly bad at it.   SQL Server was originally SYBASE, which of course is one of the big players in the RDBMS market.  

arbert is quite right though.  There are 101 ways to screw up concurrency from an application standpoint.  To properly architect a database application, one has to understand when the database is going to issue locks, what kind they are, what competing operations can be done or will be blocked, and when the locks will be released.  You also have control over the granularity of the locks (table/page/row) as well as the transaction isolation level, which controls when locks are issues and how they behave.

There are several rules of thumb:
-  Avoid using commited reads
-  Always lock tables in the same sequence (to avoid deadlocks)
-  Lock at the page level for most applications
-  Lock at the row level only for workflow queues and similar constructs
-  Use optimistic locking for most client server applications
-  Don't try and build "application locking" schemes if you can avoid it.  They are messy, ineffeciant, and are a pain to keep clean
-  Set the lock wait timeout to a reasonable value instead of the default forever
-  Don't get too wound up about dirty reads for reports.  Analyze your application usage patterns, the runtime of the report(s) and the likelyhood
   of having a dirty read actually affect anything.  Way that against the downside (if there is any measurable) of having a report be slightly off
   once in a while.

Read the manual sections on Transactions and Transaction management as a good starting point.

Bill
0
 
LVL 7

Assisted Solution

by:FDzjuba
FDzjuba earned 360 total points
ID: 11694998
SQL Server concurrency is far from perfect, however it is in place. The biggest problem of parallel processes is of course appeares to be locking rows, pages or entire table. You can try to play around with locking handling in your queries. Actually the link to an article above, explains pretty well locking advances and down sides, but doesn't have a technical information on sql server concurrency, you require.
And finally the answer to your question:) to the best of my knowledge SQL server does not manage concurrency control, these attributes can be defined using either Transact-SQL statements or through the properties and attributes of the database APIs such as ADO, OLE DB, and ODBC. There are probably some existance of default set of instruction on how to handle concurrency, which sql server uses if nothing has been said about it in the query statement, but to be honest I couldn't find any information regarding default concurrency control.
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 280 total points
ID: 11695001
I guess the key word in your question is "automatically".

The answer is "sort of". The reality is that you can't escape building it into your own code. It's no good hoping that the database server will somehow sort it all out. You need to plan for transactions, sequences of updates that all work or all fail. You need to handle errors caused by timeouts where someone else has locked a table you are trying to access.

The server is pretty clever, but you still have to tell it what to do.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question