Currently i am wondering, why some times ADO.NET throws directly a table lock exception, when a table is locked and some times the executed statements waits, until the table is not locked any more. When does a table lock cause an exception and when not? Sadly i have no code to reproduce both situations.
Is there any rule in Sybase SQL anywhere which specify this behaviour?
Thank you all!
Sybase and MsSql share the same historical code base. Back in the old days (pre sql 2000) and Sybase of the same era. Neither vendor solved their locking issues. They could each get in a dead lock situation. The problem was the logic they used. Imagine a dead lock situation like driving a car at a 4 way stop. Imagine if the rules say yield to the right when two records(cars) come in at the same time. That works fine except in situations where 4 cars arrive all at the same time each trying to yield to the car on the right. You get a deadlock situation. Back in the day a DBA would have to select a record to kill that transaction (blocking its completion) to allow the deadlock to clear. Later MsSql implemented logic where it automatically selects a record to be killed. They also improved their locking logic so deadlocks are much less frequent with future versions. So sometimes it works and sometimes it does not work is driven by was your record the one that was selected to be terminated or did the other dead locks finish allowing your record to be processed.
Oracle does better locking logic. They keep multiple copies of a given field depending on if multiple processes are modifying it at the same time. When you do a select you get the records that have already been committed not long running incomplete processes that the Sybase/MsSql locking scheme wait for completion on. Later MsSql versions do much better than Sql 7 and sql 2000 where it was a bigger problem. Sybase may have undergone the same improvements.
There are ways to reduce this locking issue by breaking out the large table into smaller tables and updating from the smaller table to the larger table in batches. Think of it as a left and right table. When your doing high volume inserts into the active left table the right table is being inserted into the main table. Then you switch and you do high volume inserts to the right table while the left table is being moved to the main table. This breaks up transactions between tables and reduces the chances of multiple processes hitting the same table at the same time (reducing deadlocks). But it means that you don't have the most current data as you have incompletely processed records in your left and right tables.