I don't know the exact answer to this, but I've always explained it like this ...
When you hit cancel in the dialog box, Tableau sends the cancel command to the database connection. The database MUST be listening for a new command, if it's busy querying data or some other task and it's not listening Tableau "freezes" waiting for the database to acknowledge the cancel request.
On some databases that I've worked with previously, we quickly learned to NEVER press cancel because it just never worked. Is that a problem with the ODBC driver or the database itself ? I've never bothered to figure it out, there's not much you can do to fix it, you just have to live with it.