Рубрика «MySQL»

Страница 4 из 6« Первая...«23456»

Блокировка таблиц

19 Июль 2008, 13:35

Для таблиц типа MyISAM использование транзакций недоступно. Однако их можно эмулировать при помощи операторов LOCK TABLES И UNLOCK TABLES. В отличие от полноценных транзакций, данные операторы блокируют всю таблицу, в результате чего никто не может работать с таблицами до тех пор, пока они остаются заблокированными. Оператор LOCK TABLES выполняет блокировку таблиц, а UNLOCK TABLES снимает блокировку.

Замечание
Все таблицы, заблокированные в текущем соединении, разблокируются при повторном вызове оператора LOCK TABLES.

Замечание
Операторы LOCK TABLES и UNLOCK TABLES имеют синонимы LOCK TABLE и UNLOCK TABLE соответственно.

LOCK TABLES categories WRITE;
INSERT INTO categories VALUES (NULL, 'Мониторы');
INSERT INTO categories VALUES (NULL, 'Принтеры');
UNLOCK TABLES;

Листинг демонстрирует блокировку таблицы categories на время добавления данных в таблицу. Следует обратить внимание, что после оператора LOCK TABLES указывается имя блокируемой таблицы, при снятии блокировки указания имени таблицы уже не требуется. Основная причина применения блокировки таблицы при помощи LOCK TABLES - это увеличение скорости обновления таблиц и добавления больших объемов данных.

При использовании блокировок можно явно указать тип блокировки - на чтение (READ) или на запись (WRITE). Различие между блокировками на чтение и запись заключается в том, что и клиент, установивший блокировку на чтение, и остальные клиенты могут только читать из таблицы данные. При блокировке на запись установивший ее клиент может как вносить записи в таблицу, так и читать, в то время как доступ других клиентов блокируется. Причем все остальные клиенты ожидают, когда блокировка будет отменена оператором UNLOCK TABLES.

Один оператор LOCK TABLES может блокировать сразу несколько таблиц, причем рекомендуется блокировать все таблицы, которые участвуют в запросах внутри блокировки. Это связано с тем, что пока блокировка, установленная LOCK TABLES, активна, невозможно получить доступ ни к каким таблицам, которые не были блокированы этим оператором.

Блокировки по записи (WRITE) имеют более высокий приоритет, чем блокировки по чтению (READ), чтобы гарантировать, что обновления данных пройдут как можно быстрее. Это означает, что если один или несколько клиентов устанавливают блокировку на чтение (READ), а затем другой клиент устанавливает блокировку на запись (WRITE) по этим же таблицам, то остальные клиенты будут ожидать, пока блокировка по записи не будет снята.

Рубрика: MySQL  |  Ваш отзыв

Транзакции в MySQL

17 Июль 2008, 14:44

Очень часто изменения базы данных требуют выполнения нескольких запросов. Так, при покупке товара в электронном магазине требуется добавить запись в таблицу orders (заказы) и уменьшить число товарных позиций на складе (таблица products). В промышленных базах данных одно событие может затрагивать гораздо большее число таблиц и требовать выполнения многочисленных запросов.

Если при выполнении одного из запросов происходит сбой, это может нарушить целостность базы данных. Например, товар может быть продан, а число товарных позиций на складе не обновлено; деньги могут быть сняты с одного счета, но не перечислены на другой и т.п. Чтобы сохранить целостность базы данных, все изменения должны выполняться как единое целое - либо все изменения успешно выполняются, либо, в случае сбоя хотя бы одного из запросов, база данных принимает состояние, которое было до начала изменений.

Транзакция - это последовательность операторов SQL, выполняющихся как единая операция, которая не прерывается другими клиентами. То есть, пока происходит работа с записями таблицы (их обновление или удаление), никто другой не может получить доступ к этим записям.

Таблицы ISAM, MyISAM и HEAP не поддерживают транзакции. В настоящий момент их поддержка осуществляется только в таблицах BDB и InnoDB.

Транзакции позволяют объединять операторы в группу и гарантировать, что все операции внутри группы будут выполнены успешно. Если часть транзакции выполняется со сбоем, результаты выполнения всех операторов транзакции до места сбоя отменяются, приводя базу данных к виду, в котором она была до выполнения транзакции.

Следует обратить внимание, что транзакции имеют смысл только в случае с типами таблиц, которые их поддерживают: InnoDB и BDB. Если существующие таблицы имеют другой тип, например, MyISAM, для работы с транзакциями его следует изменить:

ALTER TABLE orders ENGINE = INNODB;
ALTER TABLE products ENGINE = INNODB;

Следует внимательно следить, чтобы при изменении типа MyISAM на любой другой в таблице отсутствовали FULLTEXT-индексы, т.к. никакой другой тип таблиц их не поддерживает.

По умолчанию MySQL работает в режиме автоматического завершения транцакций, т.е. как только выполняется оператор обновления данных, который модифицирует таблицу, MySQL тут же сохраняет изменения на диске. Для объединения в транзакцию нескольких операторов необходимо отключить этот режим. Это можно осуществить при помощи системной переменной AUTOCOMMIT:

SET AUTOCOMMIT=0;

После отключения режима автоматического завершения транзакций следует использовать оператор COMMIT, чтобы сохранить изменения на диске, либо оператор ROLLBACK, чтобы отменить изменения, выполненные с момента начала транзакции. Для того, чтобы включить обратно режим автоматического завершения транзакций, необходимо выполнить оператор

SET AUTOCOMMIT=1;

Для того, чтобы включить режим автоматического завершения транзакций только для отдельной последовательности операторов, можно воспользоваться оператором START TRANSACTION

START TRANSACTION;
SELECT @total := count FROM products WHERE id_prd = 17;
UPDATE products SET count = @total - 1 WHERE id_prd = 17;
COMMIT;

После выполнения оператора START TRANSACTION режим автоматического завершения транзакций остается включеным до явного завершения транзакции с помощью оператора COMMIT или отката транзакции посредством ROLLBACK.

Оператор START TRANSACTION появился в MySQL начиная с версии 4.0.11 и имеет два синонима: BEGIN и BEGIN WORK, появившихся еще в версии 3.23. Однако рекомендуется использовать именно START TRANSACTION.

Рубрика: MySQL  |  1 отзыв

Постраничный вывод из MySQL

9 Июль 2008, 10:23

Как сделать постраничный вывод из MySQL: по 10 (20, 30) записей на страницу, а внизу - ссылки на остальные страницы?

Чтобы получить нужные записи, воспользуемся оператором LIMIT, который вызывается с двумя параметрами - с какой записи начинать, и сколько выводить:

SELECT id, title FROM items LIMIT 0, 10;

Этот запрос вернет записи с первой по 10, поскольку нумерация начинается с 0. Cоответственно, запросы для второй и третьей страницы будут выглядеть

SELECT id, title FROM items LIMIT 10, 10;
SELECT id, title FROM items LIMIT 20, 10;

Как видите, нам надо лишь передать в скрипт число, которое потом подставить в запрос.

Для построения постраничной навигации нам еще понадобится общее число записей в таблице. Это можно сделать с помощью запроса

SELECT COUNT(*) FROM items;
$query = "SELECT COUNT(*) FROM items";
$res = mysql_query( $query );
$total = mysql_result( $res, 0, 0 );

Далее определим, сколько всего получится страниц:

$cnt_pages = ceil( $total / ITEMS_PER_PAGE );

Здесь используется константа ITEMS_PER_PAGE, в которой хранится количество выводимых на странице записей.

<?php
$dblocation = "localhost";   // Имя сервера
$dbuser     = "root";        // Имя пользователя
$dbpswrd    = "";            // Пароль
$dbname     = "catalog";     // Имя базы данных

DEFINE('ITEMS_PER_PAGE', 5);

// Соединение с сервером базы данных
$dblink = mysql_connect( $dblocation, $dbuser, $dbpswrd );
mysql_query( 'SET NAMES cp1251' );
// Выбираем базу данных
mysql_select_db( $dbname, $dblink );

// Выбираем из БД общее количество записей
$query = "SELECT COUNT(*) FROM products WHERE 1";
$res = mysql_query( $query );
$total = mysql_result( $res, 0, 0 );
   
// Проверяем передан ли номер текущей страницы
if ( isset($_GET['page']) ) {
  $page = (int)$_GET['page'];
  if ( $page < 1 ) $page = 1;
} else {
  $page = 1;
}

// Сколько всего получится страниц
$cnt_pages = ceil( $total / ITEMS_PER_PAGE );
if ( $page > $cnt_pages ) $page = $cnt_pages;
// Начальная позиция
$start = ( $page - 1 ) * ITEMS_PER_PAGE;

$query = "SELECT id, title, price
          FROM products
          ORDER BY price ASC
          LIMIT ".$start.", ".ITEMS_PER_PAGE;
$res = mysql_query( $query );

// Выводим "шапку" таблицы
echo '<table border="1" cellpadding="5" cellspacing="0">';
echo '<tr>';
echo '<th>ID</th>';
echo '<th>Наименование</th>';
echo '<th>Цена</th>';
echo '</tr>';

while( $prd = mysql_fetch_array( $res ) )
{
    echo '<tr>';
    echo '<td>'.$prd['id'].'</td>';
    echo '<td>'.$prd['title'].'</td>';
    echo '<td>'.$prd['price'].'</td>';   
    echo '</tr>';
}

echo '</table>';

// Строим постраничную навигацию
if ( $cnt_pages > 1 )
{
    echo '<div style="margin:1em 0">&nbsp;Страницы: ';
    // Проверяем нужна ли стрелка "В начало"
    if ( $page > 3 )
        $startpage = '<a href="'.$_SERVER['PHP_SELF'].'?page=1"><<</a> ... ';
    else
        $startpage = '';
    // Проверяем нужна ли стрелка "В конец"
    if ( $page < ($cnt_pages - 2) )
        $endpage = ' ... <a href="'.$_SERVER['PHP_SELF'].'?page='.$cnt_pages.'">>></a>';
    else
        $endpage = '';

    // Находим две ближайшие станицы с обоих краев, если они есть
    if ( $page - 2 > 0 )
        $page2left = ' <a href="'.$_SERVER['PHP_SELF'].'?page='.($page - 2).'">'.($page - 2).'</a> | ';
    else
        $page2left = '';
    if ( $page - 1 > 0 )
        $page1left = ' <a href="'.$_SERVER['PHP_SELF'].'?page='.($page - 1).'">'.($page - 1).'</a> | ';
    else
        $page1left = '';
    if ( $page + 2 <= $cnt_pages )
        $page2right = ' | <a href="'.$_SERVER['PHP_SELF'].'?page='.($page + 2).'">'.($page + 2).'</a>';
    else
        $page2right = '';
    if ( $page + 1 <= $cnt_pages )
        $page1right = ' | <a href="'.$_SERVER['PHP_SELF'].'?page='.($page + 1).'">'.($page + 1).'</a>';
    else
        $page1right = '';

    // Выводим меню
    echo $startpage.$page2left.$page1left.'<strong>'.$page.'</strong>'.$page1right.$page2right.$endpage;

    echo '</div>';
}
?>

Тут есть проблема, о которой стоит упомянуть: кроме переменной $page нашему скрипту могут быть переданы и другие переменные. Решается это просто:

$uri = strtok($_SERVER['REQUEST_URI'],"?")."?";
if (count($_GET)) {
  foreach ($_GET as $k => $v) {
    if ($k != "page") $uri.=urlencode($k)."=".urlencode($v)."&";
  }
}

и полученную переменную $uri подставляем в код вместо $_SERVER['PHP_SELF']

Рубрика: MySQL, PHP  |  Отзывов: 17
Страница 4 из 6« Первая...«23456»