Solved

SQL concurrency

Posted on 2004-08-02
6
681 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
6 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
0
 
LVL 5

Expert Comment

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

Expert Comment

by:arbert
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Accepted Solution

by:
grant300 earned 90 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now