SQL concurrency

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
LVL 4
sublimationAsked:
Who is Participating?
 
grant300Commented:
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
 
Thandava VallepalliCommented:
0
 
hkamalCommented:
Answer to original question is Yes. With potential for problems as dirty cache is an issue
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arbertCommented:
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
 
FDzjubaCommented:
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
 
crescendoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.