Hello,
When running a stored procedure (from a .NET application) that does an INSERT and an UPDATE, I sometimes (but not that often, really) and randomly get this error:
ERROR [40001] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Your server command (family id #0, process id #46) encountered a deadlock situation. Please re-run your command.
How can I fix this?
Thanks.
Assuming that your tables are properly indexed (and that you are actually using those indexes - always worth checking via the query plan) you could try breaking the component parts of the SP down and wrapping them in separate transactions so that each unit of work is completed before the next one starts.
begin transaction
update mytable1
set mycolumn = "test"
where ID=1
commit transaction
go
begin transaction
insert into mytable2 (mycolumn) select mycolumn from mytable1 where ID = 1
commit transaction
go
Your best bet for solving you deadlocking issue is to set "print deadlock information" to on using
sp_configure "print deadlock information", 1
Everytime there is a deadlock this will print information about what processes were involved and what sql they were running at the time of the dead lock.
If your tables are using allpages locking. It can reduce deadlocks to switch to datarows or datapages locking. If you do this make sure to gather new stats on the tables and recreate indexes, views, stored procedures and triggers that access the tables that are changed. If you don't you will either get errors or not see the full benefits of the change depending on which ones are not recreated.
I have a set of long term apps which occasionally over lap table access and sybase will throw this error. If you check the sybase server log it will give you the complete info on why it happened. Like: The sql that was involved the two processes trying to get a lock. Usually one trying to read and the other doing something like a delete. In my case the apps are running in separate JVMs, so can't sychronize just have to clean up periodically.
Chris and Mark are right. Not only do you demonstrate a lack of understanding, you have not done the research requested.
"Well that's the whole point, Mark. I'm saying it /should/ be delayed, as in, that is the behaviour that is expected."
Assuming you have an IT job, and you are responsible for fixing this problem then ... It actually does not matter what you think or expect. The server is doing something else, which is documented widely, which you need to familiarise yourself with, which will then change what you think and expect.
Locks and blocking locks cause delays, and evidently that is not happening (regardless of whether you think it should). If they were happening, then you would experience delays. You are experiencing something else, which is currently unknown to you.
For understanding: if you think the server should behave differently, you are welcome to write your own server, but until then, we need to accept that your company has paid good money for a server with over one hundred man years of real engineering in the codeline, and is famously standards-compliant. So it is not going to change anytime soon; the errant code has to change. And before you can do that you need to learn (a) what caused the problem and (b) the solution, so that your change is effective. That solution is over 30 years old (from my own personal experience).
And please, let's not argue about the theory or your expectations, it is simple. Make no mistake, if and when you understand a deadlock, the simple proof is, you will be able to intentionally write one in SQL, in 30 seconds (less than 10 lines). Then, and only then, will you be able to write code that eliminates deadlocks.
PRINT DEADLOCK INFO is correct, but even then, if you do not understand what a deadlock is, you will have trouble dealing with the info logged by Sybase. There is absolutely nothing "random" about it, two opposing code segments are locked in a deadly embrace, and will be forever, which Sybase detects, and resolves, by killing one of them, so the other can continue (otherwise you will have two spids hanging onto locks and hanging up the log forever).
Sybase and MS do not create deadlocks (there is no deadlock on the installation CD). Someone wrote SQL some code (with the deadlock in it, unwittingly) and submitted to the server. Deadlocks are a result of the application code "design" that has been chosen or implemented naïvely AND not implementing ordinary procedures that promote concurrency. In such systems, once you start getting deadlocks, you can only expect it to increase. As you have already seen.
==========
Re "If your tables are using allpages locking. It can reduce deadlocks to switch to datarows or datapages locking."
Do you have evidence that switching from APL to DPL/DRL reduces deadlocks ?
It does not. DPL/DRL was designed to reduce Index locks only. It actually creates much more locks. Second, DPL/DRL introduce a whole new level of Fragmentation (that APL does not have). Third, APL tables do not require REORG_REBUILD; DPL/DRL does (which interferes with the log sequence, uses a table lock, and substantally increases the maintenance window). Fourth, although DPL/DRL is slightly faster for INSERT/DELETES only, APL is much faster overall. Last, whatever you do, ensure that you measure (sp_sysmon) both before and after any change.
Re "you could try breaking the component parts of the SP down and wrapping them in separate transactions so that each unit of work is completed before the next one starts"
This site is for professional programmers, right ?
You cannot possibly be serious. By splitting Transactions like that, you have just broken what was supposed to be a Atomic Unit of Work, and thus you no longer have transactional integrity. When the business and the Auditors catch up with you, you might not have a job.
What I do not understand is, since the "transactions" are now two single atomic commands, and therefore the 2 x BEGIN TRANs and 2 x COMMIT TRANs are superfluous (they have no effect since each "transaction" is now a single command), why have you left them there, why have you not removed them entirely. It is confusing because you might THINK you have transaction control, but actually you have none at all. You can remove the 2 x GOs as well. The core truth re what you are suggesting in your code block is:
UPDATE ...
INSERT ...
Want to get rid of deadlocks ? Just remove the transaction ! Want to get rid of performance problems ? Just remove the code ! Want to remove error messages ? Just remove the programmer ! The state of the profession these days, OMG.