[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Handling concurrency in Webservice

Posted on 2004-10-01
Medium Priority
Last Modified: 2012-06-27
I have webservice which updates the customer details I 'am using sybase 8 as as backend and .net as platform for developing webservice. while testing  in a multiuser environment I got some locked table error.
I tried setting the isolation level for database option.

For this, I have tried doing...
 when there are update/Insert/Delete actions necessary,
 immediately after starting the transaction,I  locked the tables  involved (in sequence to avoid deadlock).
 if a lock fails, I'am retrying it after x milliseconds

Even then

I a'm getting some serialization failure:deadlock detected error.

It would be a great help if somebody comes with solution to handle this concurrency.

Question by:ranganathanp
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
  • 3
  • 2
LVL 12

Expert Comment

ID: 12198755
I see this as more of a Sybase question. Handle the concurrency at the Sybase or at the driver level.
Your web services shouldn;'t have to deal with that. They make a call to the DB through ADO.NET (or whatever) and that layer or some layer below that should retry.

You may want to look into why you are seeing locking problems at all. They should be pretty rare.
If you are locking the entire table to do an insert then that may be the probme. Lock as little as you can, ideally only the record you are editing (I have no idea if Sybase supports record-level or page-level locking).
You only need to lock if there is a possibility of a real clash. For example if it is realistically possible for there to be 2 overalpping calls to update the same customer's details. Overlapping calls that update different customers should not require locking.
The DB may lock some records or pages in whenever you modify data e.g. in the pages that stroe the indexes but that is seldom a significant problem.
Another factor in locking is to minimise the amount of time the locks are held for.
Alos consider how critical it is that you avoid all clashes. If you get a dirty read (reading a partially edited record) once every million reads, is that a significant problem? In a system processing financial data it would be. If it in your case? If not, then you might well decide not to do anything about it.

Author Comment

ID: 12223817
Hi monosodiumg,

Thanks for your reply.
Sybase does not support row-level locking (for version < 11.9) , yes I 'am using the sybase 8.0
so, in this version only page-level & table level locking is possible. The actaul scenerio is that Biztalt server sends the requests which, might send in a way which is less than 5 ms, so when a 2 or more update requests are sent to the webservice for the same customer, then there is possibility of overlapping the requests. I 'am aware of that the db may lock some records while updating , but can you tell me how the explicit locking can be done, so that I could lock the table, with some timeouts while updating a record without ending up into deadlocks.


Author Comment

ID: 12295834
any luck dot net gurus.............?
LVL 12

Accepted Solution

monosodiumg earned 500 total points
ID: 12296372
>but can you tell me how the explicit locking can be done,
I don't know sybase but i believe its fairly close to SQL server.
Locking is too big a topic to outline in a post like this. There are lots of issues around locking. When you insert or update a record, you may need to lock records in the data table and possibly sevaral pages for each index that is potentially affected. It also depends on the the isolation level required and the number of records affected.

>so that I could lock the table,
You really do not need to do this. The databases takes care of this and it does it very well.

Here are a few extracts from the overview page on locking from the SQL server docs.
SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query; for example one table may be very small and have a table lock applied, while another, larger table may have row locks applied. The level at which locks are applied does not have to be specified by users and needs no configuration by administrators.
There are several lock modes: shared, update, exclusive, intent, and schema. The lock mode indicates the level of dependency the connection has on the locked object. SQL Server controls how the lock modes interact. For example, an exclusive lock cannot be obtained if other connections hold shared locks on the resource.
SQL Server has an algorithm to detect deadlocks, a condition where two connections have blocked each other. If an instance of SQL Server detects a deadlock, it will terminate one transaction, allowing the other to continue.
SQL Server has an algorithm to detect deadlocks, a condition where two connections have blocked each other. If an instance of SQL Server detects a deadlock, it will terminate one transaction, allowing the other to continue.
If you choose to lock the tables yourself, you will probably seriously reduce the perfomance under heavy load and the likelihood of contention will be much higher.
If youa re currently experienceing locking problems and your are explicitly locking resources, then the soluton may be to remove any explicit locking. In TSQL at least, you can specify lock types (about a dozen types) in queries. On thte page detailing those, it states:
The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.
LVL 12

Expert Comment

ID: 12542341
I think I've provided enough to warrant to points on this one in the absence of any statement to the contrary from the asker.

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 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