Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ?

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hey Monks,

Using Sybase::CTlib ( Sybase Client Library API ) for posting data into a sybase database, can we implement Deadlock Retry Mechanism in Bulk- Copy Routines (BLK API) mode ?

I am doing BCP ( Bulk Copy ) using following BLK operations : blk_init(),blk_rowxfer(),blk_done() and blk_drop(). Wondering if the above API keeps track of the failed records in Bulk Copy Mode and inserts only those records upon retry.

Please advise.

Comment on Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ?

Replies are listed 'Best First'.
Re: Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ? by JavaFan (Canon) on Jul 21, 2011 at 12:45 UTC
It's been a while since I last used Sybase::*, but IIRC, its API is just a thin layer over the C library. Which means that if you can do it in C, you can do it in Perl in the same way. I've always found the Sybase documentation quite good; I'd start searching in there whether it can be done, and if it can be done, how. The translation of C function calls to Perl subroutine calls should be easy.	[reply]
Re^2: Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ? by Anonymous Monk on Jul 21, 2011 at 14:15 UTC
Thanks Monk, If my understanding is correct - even if it can be done in C and then translated into perl, means we have to either touch Sybase::CTlib or subclass it, is that correct? Just to be clear, could you please confirm that we don't have any way to achieve implementing retry on deadlock situations by using just the existing functionalities of Sybase::CTlib?	[reply]
Re^3: Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ? by JavaFan (Canon) on Jul 21, 2011 at 14:54 UTC
That's not at all what I have been saying. In fact, what I said was "if it's possible to do it using the C library, it ought to be possible using Sybase::CTlib". Or, phrased differently, if it's not possible using the C library, it's not possible using the Perl layer to said library.	[reply]
Re: Is it possible to implement Deadlock Retry Mechanism with Sybase::CTlib in BLK Copy Mode ? by mpeppler (Vicar) on Jul 23, 2011 at 14:20 UTC
a deadlock retry mechanism is essentially down to checking for a deadlock having occurred in a transaction, and resubmitting that transaction if that's the case. In Sybase::CTlib this means setting up a server message callback with some code that checks for error 1205 (deadlock), and re-sends the transaction that failed if that error code is detected. For blk_xxx() calls this means that you'll need to "remember" the rows that you sent between blk_done() calls so that you can re-send those rows in case a deadlock is detected. All of this can be written in perl and in your own code - no real need to subclass Sybase::CTlib (although you could do so) or delve into its C code. Michael	[reply]