Solved

SQL concurrency

Posted on 2004-08-02
6
687 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 19

Accepted Solution

by:
grant300 earned 90 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 90 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 70 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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