��ǰ�汾�IJ������������ȫ�ģ���Ϊ����û��ʶ��ȫ������������� SQL ����Ԫ���ڵ�һ�����������еġ������������Ч��֪������Ԫ ��ʱ��ʼ����ʱ��ֹ�����ԣ�������Ԫ�У��������ݿ����ӻᱻ�л���
���Ӧ��û���趨����Ԫ�༭����ôû���κ� MySQL ���ؾ����ܹ��������
����ͨ�� SQL hints �����������ơ�����ѡ���Եĵ������� API ���м�أ� Ȼ������ API ִ�п��������������������
Example #1 ����һ�� master ��һ�� slave �IJ��
[myapp] { "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } } }
Example #2 ��������ʹ�� SQL hints
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* ���� SELECT ��ͷ������ʹ�� master */
if (!$mysqli->query("START TRANSACTION")) {
/* Please use better error handling in your code */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* ��ֹ�����л�*/
if (!$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_LAST_USED_SWITCH)))) {
/* Please do proper ROLLBACK in your code, don't just die */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if ($res = $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM test", MYSQLND_MS_LAST_USED_SWITCH)))) {
$row = $res->fetch_assoc();
$res->close();
if ($row['_num'] > 1000) {
if (!$mysqli->query(sprintf("/*%s*/INSERT INTO events(task) VALUES ('cleanup')", MYSQLND_MS_LAST_USED_SWITCH)))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
}
} else {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query(sprintf("/*%s*/UPDATE log SET last_update = NOW()", MYSQLND_MS_LAST_USED_SWITCH)))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query(sprintf("/*%s*/COMMIT", MYSQLND_MS_LAST_USED_SWITCH)))) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
$mysqli->close();
?>
�Դ� PHP 5.4.0 �汾��ʼ��mysqlnd library ��������� autocommit ģʽ�µ�״̬��������� SET AUTOCOMMIT=0 ��������䣬�⽫�ò����ʼ����������������Ϳ��Բ���ʹ�� SQl hints��
�� PHP 5.4.0 �汾��ʼ������ API autocommit ģʽ������趨���� trx_stickiness=master�� ��ô��������������Զ���ֹ���ؾ���������л����� autocommit ���õ����õ��У� ���������������н����е���䷢�� master�����ø��ؾ��⡣�� autocommit ���������Ժ�������¿�ʼ���ؾ������е���䡣
�� PHP 5.5.0 �� PECL/mysqlnd_ms 1.5.0 �汾�����ּ�齫��������� mysqli_autocommit() ������ mysql_commit() �� mysql_rollback()��
Example #3 ������ظ��ؾ����µ� trx_stickiness ����
{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "trx_stickiness": "master" } }
Example #4 Transaction aware
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* ���� autocommit�������������䷢�� master */
$mysqli->autocommit(FALSE);
if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
/* Please do proper ROLLBACK in your code, don't just die */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if ($res = $mysqli->query("SELECT COUNT(*) AS _num FROM test")) {
$row = $res->fetch_assoc();
$res->close();
if ($row['_num'] > 1000) {
if (!$mysqli->query("INSERT INTO events(task) VALUES ('cleanup')")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
}
} else {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->query("UPDATE log SET last_update = NOW()")) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
if (!$mysqli->commit()) {
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
}
/* ����ж�������ֹ���������ø��ؾ��� */
$mysqli->autocommit(TRUE);
$mysqli->close();
?>
Note: �汾����
trx_stickiness=master ������Ҫ PHP 5.4.0 ���ϰ汾��
���Բο���������� transaction handling �½��С�