Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

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
0
sublimation
Asked:
sublimation
3 Solutions
 
Thandava VallepalliCommented:
0
 
hkamalCommented:
Answer to original question is Yes. With potential for problems as dirty cache is an issue
0
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now