thekid/rfc — Issue #159: RFC 0159: Deadlock handling
Author: thekid
Created: 2011-10-09 15:29:19+00:00
State: open
Labels: implemented, rfc

Scope of Change
====
A new class rdbms.SQLDeadlockException will be introduced. The rdbms
drivers will handle dead lock situations specially and throw this new
exception instead of a generic SQLStatementFailedException.

Rationale
====
Deadlocks are a very special case in database systems and often need
special programmatic handling.

Functionality
====

Inheritance
----

```
lang.Throwable
`- lang.XPException
`- rdbms.SQLException
`- rdbms.SQLStatementFailedException
`- rdbms.SQLDeadlockException

```
Example (current usage)
----

```php
begin(new Transaction('update'));
$conn->query('...');
$conn->query('...');
} catch (SQLStatementFailedException $e) {
if (1205 == $e->getErrorcode()) {
// Handle deadlock
} else {
$tran && $tran->rollback();
// Handle other statement errors
}
} catch (SQLException $e) {
$tran && $tran->rollback();
// Handle other errors
}
?>
```
Problems with this approach:
* The error code is driver-specific
* Complicated sourcecode

Sybase
----
* Error code: 1205
* Can be retrieved by: sybase_query('select @@error')

See also [http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/24868;pt=24673/* Error #1205]

MySQL
----
* Error code: 1213 (SQLSTATE: 40001 (ER_LOCK_DEADLOCK))
* Can be retrieved by: mysql_errno()

See also [http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL Server error messages].

PostgreSQL
----
* Error code: 40P01 DEADLOCK DETECTED
* Can be retrieved by: pg_result_status() / pg_result_error_field()

See also [http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html PostgreSQL Errorcodes Appendix]

Example (new functionality)
----

```php
begin(new Transaction('update'));
$conn->query('...');
$conn->query('...');
} catch (SQLDeadlockException $e) {
// Handle deadlock
} catch (SQLException $e) {
$tran && $tran->rollback();
// Handle other situations
}
?>
```

Security considerations
====
n/a

Speed impact
====
Neglectible.

Dependencies
====
n/a

Related documents
====
- http://www.xaprb.com/blog/2006/08/08/how-to-deliberately-cause-a-deadlock-in-mysql/
- http://www.blacksheepnetworks.com/security/resources/bossconsulting/sybase_dba/sublevels/deadlock.info
- http://www.compuspec.net/reference/database/sybase/deadlock.shtml
- http://xp-framework.net/rfc/contrib/rfc0159.diff

Comments
====