the syb_flush_finish parameter in DBD::Sybase

When I was looking up the syntax for a parameter on Michael Peppler’s DBD::Sybase perl module, I ran across his explanation of the syb_flush_finish parameter.  I just had to explain what was being reported to him 🙂

syb_flush_finish (bool)

If $dbh->{syb_flush_finish} is set then $dbh->finish will drain any results remaining for the current command by actually fetching them. The default behaviour is to issue a ct_cancel(CS_CANCEL_ALL), but this appears to cause connections to hang or to fail in certain cases (although I’ve never witnessed this myself.)

What was being reported to Michael Peppler was most likely the result of how cancels were performed in TDS version 4.x. In TDS 4, the cancel was performed using the TCP expedited flag (similar to the OUT OF BAND flag. Which caused ASE to cancel whatever operation it was currently doing and in some cases, this would involve the connection going into an unknown state.

For example:

My application is sending multiple batches of operations (dbcc checkalloc, a few select and update statements, and executing three stored procedures) to the ASE server.

Unless I programmed my application to know what batch is currently executing on the ASE server, the application thinks the last stored procedure is executing but in reality, ASE is currently happily running the dbcc checkalloc. Keep this in your head 🙂

The user sees that the application is ‘hanging’ or is ‘slow’ because ASE is running the dbcc checkalloc but they just wanted the output of a stored procedure… so they hit the cancel button and attempt the stored procedure again.

Behind the scenes, the client application will send a TDS cancel packet encapsulated within a TCP packet marked as expedited. Think of it as a TDS packet with turbo 😉

Remember that ASE is still working on the dbcc checkalloc? Well, the other operations are sitting in what is called the recieve buffer. ASE is happily chugging away on the checkalloc when ASE is notified by the os that there is an expedited (VIP) packet waiting for it. ASE retrieves the TDS cancel packet, identifies which connection it belongs to and cancels the CURRENTLY RUNNING OPERATION and flushes the receive buffer.

Now, normally that is perfectly fine except what if the receive buffer was full and the application was attempting to send more operations for ASE to do?

Receive buffer contents:

batch #1 dbcc checkalloc(mydb)
batch #2 select name, author from authors where 1 = 2
select name, dbid from master..sysdatabases where dbid = 4
batch #3 update authors set phone = 911 where status = "dying"
update authors set status = ‘dead’ where phone_status = ‘busy’
batch #4 exec sp_set_author ‘rabbit’, ‘king’, ‘toe’
batch #5 exec sp_flushstats

*** receive buffer full ***

waiting to send to ASE:

rest of batch #5: authors
batch #6 exec sp_set_author ‘rabbit’, ‘king’, ‘foot’

In this scenerio, where the last batch of in the receive buffer is incomplete AND a TDS cancel (TDS v4) is sent, the result will be:

receive buffer:

batch #1 authors
batch #2 exec sp_set_author ‘rabbit’, ‘king’, ‘foot’

In this case, a rather benign syntax error will be raised and sent to the application. In other cases, ASE could get so terribly confused as to the batch in the newly flushed receive buffer, it throws up its hands with stacktraces and/or just dropping the connection entirely.

Sybase largely worked around this problem with TDS 5

The TDS cancel packet is sent like any other TDS packet:

The packet will enter the receive buffer and when ASE periodically scans the receive buffer, it will see the TDS cancel and cancel all operations up to the point that the TDS cancel packet was sent (including any partial operations).

This brings up a not so small issue of what happens when the receive buffer is full when the application sends the TDS cancel packet? Well, until space is made available in the receive buffer (the current operation finishes – not necessarily a whole batch), the TDS cancel packet will sit on the client side. The cancel will appear to hang.

This is covered in the TDS 5 specification but with more technical mumbo jumbo 😉

So how can we force the application to use TDS 5? Specify it on the DBI->connect() string:

$dbh=DBI->connect("dbi:Sybase:tdsLevel=CS_TDS_50", $user, $password); 
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *