PROCEDURE DAN FANCTION MYSQL

Rutinitas Stored (Prosedur dan Fungsi) didukung pada versi MySQL 5.0. Stored Prosedur adalah satu set pernyataan, yang memungkinkan kemudahan dan fleksibilitas bagi programmer karena prosedur yang tersimpan mudah untuk menjalankan dari menerbitkan kembali sejumlah pernyataan SQL individu. Prosedur tersimpan bisa memanggil prosedur lain yang tersimpan juga. Prosedur tersimpan bisa sangat berguna di mana beberapa aplikasi klien ditulis dalam bahasa yang berbeda atau bisa bekerja pada platform yang berbeda tetapi mereka harus melakukan operasi database yang sama.

Prosedur Store dapat meningkatkan kinerja karena dengan menggunakan informasi prosedur yang tersimpan kurang perlu dikirim antara server dan klien. Hal ini meningkatkan beban pada server database karena bekerja kurang dilakukan pada sisi client dan pekerjaan banyak dilakukan pada sisi server.

Syntax membuat PROCEDURE :
Syntax umum membuat PROCEDURE adalah :
        CREATE PROCEDURE proc_name ([proc_parameter[......]]) routine_body

proc_name : nama procedure
proc_parameter : [ IN | OUT | INOUT ] nama type parameters
routine_body : Valid SQL procedure statement


Daftar parameter yang tersedia dengan dalam kurung. Parameter dapat dideklarasikan untuk menggunakan tipe data yang valid, kecuali bahwa atribut Collate tidak dapat digunakan. Secara default setiap parameter adalah parameter DI. Untuk menentukan jenis lain parameter yang digunakan atau kata kunci OUT IN sebelum nama parameter.

DI parameter digunakan untuk melewatkan nilai ke dalam prosedur. Prosedur dapat mengubah nilai tetapi ketika prosedur ini mengembalikan nilai maka modifikasi tidak terlihat ke pemanggil. OUT parameter digunakan untuk melewatkan nilai dari prosedur ke pemanggil tetapi terlihat kepada pemanggil. Parameter IN diawali dengan penelepon dan dapat dimodifikasi dengan prosedur, dan setiap perubahan dibuat oleh prosedur ini terlihat ke pemanggil.

Untuk setiap parameter OUT atau IN Anda harus mengoper variabel yang ditetapkan pengguna karena kemudian prosedur mengembalikan nilai maka hanya Anda yang dapat memperolehnya nilai. Tetapi jika Anda memanggil prosedur dari prosedur yang lain maka anda juga bisa mengoper parameter rutin atau variabel sebagai DI atau parameter IN.

Routine_body berisi pernyataan SQL prosedur yang valid yang dapat pernyataan sederhana seperti SELECT atau INSERT atau mereka dapat menjadi pernyataan senyawa ditulis menggunakan BEGIN dan END. Pernyataan majemuk dapat terdiri deklarasi, loop atau struktur kontrol lainnya.

Sekarang kita menjelaskan Anda contoh prosedur yang tersimpan sederhana yang menggunakan parameter OUT. Ia menggunakan perintah klien mysql untuk mengubah pembatas pemisah pernyataan dari, ke / / prosedur hingga sedang didefinisikan. Contoh:

mysql> delimiter //
mysql> CREATE PROCEDURE Sproc(OUT p1 INT)
    -> SELECT COUNT(*) INTO p1 FROM Emp;
    -> //
Query OK, 0 rows affected (0.21 sec)

mysql> delimiter ;
mysql> CALL Sproc(@a);
Query OK, 0 rows affected (0.12 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 5    |
+------+

1 row in set (0.00 sec)

SYNTAX MEMBUAT FUNCTION

Sintaks umum Menciptakan Fungsi adalah:
         CREATE FUNCTION func_name ([func_parameter [,...]]) RETURNS routine_body jenis

func_name: Nama Fungsi
func_parameter: ketik param_name
ketik: Setiap datatype valid MySQL
routine_body: Hari SQL pernyataan prosedur

Klausa RETURN adalah wajib bagi FUNCTION. Ini digunakan untuk menunjukkan jenis fungsi kembali.

Sekarang kita menjelaskan Anda contoh sederhana fungsi. Fungsi ini mengambil parameter dan digunakan untuk melakukan suatu operasi dengan menggunakan fungsi SQL dan mengembalikan hasilnya. Dalam contoh ini tidak ada perlu menggunakan pembatas karena tidak mengandung internal; pembatas pernyataan. Contoh:

mysql> CREATE FUNCTION func(str CHAR(20))
    -> RETURNS CHAR(50)
    -> RETURN CONCAT('WELCOME TO, ',str,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT func('RoseIndia');
+------------------------+
| func('RoseIndia')      |
+------------------------+
| WELCOME TO, RoseIndia! |
+------------------------+

1 row in set (0.00 sec)

Read More

Flow Control Constructs

Flow Control Membangun meliputi IF, CASE, LOOP, WHILE, ITERATE, REPEAT and LEAVE konstruksi. Mereka adalah sepenuhnya dilaksanakan.
Konstruksi ini dapat berisi pernyataan tunggal atau suatu blok statemen menggunakan dengan BEGIN ... .. pernyataan END. Dan konstruksi ini dapat diulang juga.

IF Pernyataan

Sintaks umum IF Pernyataan:
 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ...[ELSE statement_list] END IF

IF pernyataan bersyarat menerapkan dasar membangun. Ketika search_condition ini benar maka hanya terkait SQL statement_list dijalankan tetapi jika salah maka statement_list klausa ELSE dijalankan. statement_list bisa terdiri satu atau lebih pernyataan. Contoh:

mysql> delimiter //
mysql> CREATE PROCEDURE IFProc(IN p INT)
    -> IF(p<7)
    -> THEN
    -> SELECT * FROM Client;
    -> ELSE
    -> SELECT * FROM Products;
    -> END IF
    -> //
Query OK, 0 rows affected (0.31 sec)

mysql> delimiter ;
mysql> CALL IFProc(5);
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.09 sec)
Query OK, 0 rows affected (0.11 sec)

mysql> CALL IFProc(8);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.09 sec)
Query OK, 0 rows affected (0.11 sec)

Contonh penggunaan  IF Statement dalam Select Clause. 

mysql> SELECT Name AS NAME, City AS CITY,
    -> IF(City<>'Mumbai',"Software","Bollywood") AS Profession
    -> FROM Client;
+---------------+----------+------------+
| NAME          | CITY     | Profession |
+---------------+----------+------------+
| A K Ltd       | Delhi    | Software   |
| V K Associate | Mumbai   | Bollywood  |
| R K India     | Banglore | Software   |
| R S P Ltd     | Kolkata  | Software   |
| A T Ltd       | Delhi    | Software   |
| D T Info      | Delhi    | Software   |
+---------------+----------+------------+
6 rows in set (0.02 sec)

Pernyataan CASE :

        CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
Atau
        CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

Sintaks pertama melaksanakan statement_list ketika case_value yang = when_value. Jika tidak ada case_value sesuai dengan when_value maka jalankan statement_list klausa ELSE. Dan dalam sintaks kedua, jika search_condition benar maka hanya sesuai statement_list SQL mengeksekusi tetapi jika salah maka statement_list klausa ELSE dijalankan. Contoh:

mysql> delimiter //
mysql> CREATE PROCEDURE WHENProc(IN p INT)
    -> CASE p*10
    -> WHEN 20 THEN SELECT * FROM Products WHERE Price<=7000;
    -> WHEN 30 THEN SELECT * FROM Products WHERE Price>=7000;
    -> ELSE
    -> SELECT * FROM Products;
    -> END CASE
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> CALL WHENProc(2);
+---------+-------------+------+---------+
| Prod_ID | Prod_Detail | C_ID | price   |
+---------+-------------+------+---------+
| 111     | Monitor     | 1    | 7000.00 |
| 113     | Keyboard    | 2    | 1200.00 |
| 114     | Mouse       | 3    | 500.00  |
+---------+-------------+------+---------+
3 rows in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)

mysql> CALL WHENProc(3);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
3 rows in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

mysql> CALL WHENProc(1);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

 Pernyataan LOOP

 Sintaks umum LOOP Pernyataan:
        [begin_label:] LOOP statement_list END LOOP [end_label]
Menerapkan Pernyataan LOOP loop sederhana membangun. 

Pernyataan ini dipakai untuk mengulang pelaksanaan statement_list tersebut,  

statement_list dapat berisi satu atau lebih dari satu laporan. 

Laporan bisa mengulang eksekusi loop sampai keluar dan biasanya yang 

dapat dilakukan dengan Pernyataan LEAVE. Pernyataan LOOP dapat diberi label juga.

 

 Pernyataan LEAVE

 Sintaks umum LEAVE Pernyataan:
         LEAVE label

 Pernyataan LEAVE digunakan untuk keluar dari kontrol aliran konstruksi.

 Pada contoh berikut ini kita menjelaskan Anda baik LOOP dan LEAVE Pernyataan.

mysql> delimiter //
mysql> CREATE PROCEDURE LProc()
    -> BEGIN
    -> DECLARE p INT;
    -> SET p=1;
    -> lbl: LOOP
    -> SELECT * FROM Client WHERE C_ID=p;
    -> SET p=p+1;
    -> IF p > 5
    -> THEN LEAVE lbl;
    -> END IF;
    -> END LOOP;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
      mysql> delimiter ;
mysql> CALL LProc();
+------+---------+-------+
| C_ID | Name    | City  |
+------+---------+-------+
| 1    | A K Ltd | Delhi |
+------+---------+-------+
1 row in set (0.00 sec)
      +------+---------------+--------+
| C_ID | Name          | City   |
+------+---------------+--------+
| 2    | V K Associate | Mumbai |
+------+---------------+--------+
1 row in set (0.01 sec)
      +------+-----------+----------+
| C_ID | Name      | City     |
+------+-----------+----------+
| 3    | R K India | Banglore |
+------+-----------+----------+
1 row in set (0.02 sec)
      +------+-----------+---------+
| C_ID | Name      | City    |
+------+-----------+---------+
| 4    | R S P Ltd | Kolkata |
+------+-----------+---------+
1 row in set (0.03 sec)
      +------+---------+-------+
| C_ID | Name    | City  |
+------+---------+-------+
| 5    | A T Ltd | Delhi |
+------+---------+-------+
1 row in set (0.04 sec)
      Query OK, 0 rows affected (0.04 sec)
    
  

ITERATE Statement
The general syntax of ITERATE Statement is:
        ITERATE label
ITERATE Statement can appear only within REPEAT, LOOP and WHILE Statements. ITERATE is used to iterate (Continue) the loop again. Example : 

mysql> delimiter // mysql> CREATE PROCEDURE ITERProc() -> BEGIN -> DECLARE p INT; -> SET p=1; -> lbl: LOOP -> SET p=p+1; -> IF p<5 -> THEN ITERATE lbl; -> END IF; -> SELECT * FROM Client; -> LEAVE lbl; -> END LOOP lbl; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL ITERProc(); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 4 | R S P Ltd | Kolkata | | 5 | A T Ltd | Delhi | | 6 | D T Info | Delhi | +------+---------------+----------+ 6 rows in set (0.01 sec) Query OK, 0 rows affected (0.02 sec)

REPEAT Statement
The general syntax of REPEAT Statement is:
        [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
Statement_list contains the one or more statements. REPEAT Statement is used to repeat the statement_list until the search_condition evaluates true. The REPEAT Statement can be labeled also. Example : 

mysql> delimiter // mysql> CREATE PROCEDURE REProc() -> BEGIN -> DECLARE p INT; -> SET p=1; -> REPEAT -> SELECT * FROM Products WHERE C_ID=p; -> SET p=p+1; -> UNTIL p > 5 -> END REPEAT; -> END -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> CALL REProc(); +---------+-------------+------+---------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+---------+ | 111 | Monitor | 1 | 7000.00 | +---------+-------------+------+---------+ 1 row in set (0.01 sec) +---------+-------------+------+----------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+----------+ | 112 | Processor | 2 | 11000.00 | | 113 | Keyboard | 2 | 1200.00 | +---------+-------------+------+----------+ 2 rows in set (0.02 sec) +---------+-------------+------+--------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+--------+ | 114 | Mouse | 3 | 500.00 | +---------+-------------+------+--------+ 1 row in set (0.03 sec) Empty set (0.04 sec) +---------+-------------+------+----------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+----------+ | 115 | CPU | 5 | 15500.00 | +---------+-------------+------+----------+ 1 row in set (0.04 sec) Query OK, 0 rows affected (0.05 sec)

WHILE Statement
The general syntax of WHILE Statement is:
        [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

The WHILE Statement repeats the statement_list until the search_condition evaluates true. The WHILE Statement can be labeled also. Example :

mysql> delimiter // mysql> CREATE PROCEDURE WHILProc() -> BEGIN -> DECLARE p INT; -> SET p=1; -> WHILE p <= 5 DO -> UPDATE Products -> SET Price=Price*1.03 WHERE C_ID=p; -> SET p=p+1; -> IF p=4 -> THEN -> SET p=p+1; -> END IF; -> END WHILE; -> SELECT * FROM Products; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL WHILProc(); +---------+-------------+------+----------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+----------+ | 111 | Monitor | 1 | 7426.30 | | 112 | Processor | 2 | 11669.90 | | 113 | Keyboard | 2 | 1273.08 | | 114 | Mouse | 3 | 530.45 | | 115 | CPU | 5 | 16443.95 | +---------+-------------+------+----------+ 5 rows in set (0.10 sec) Query OK, 0 rows affected (0.12 sec)   SUMBER : http://www.roseindia.net/mysql/mysql5/flow-control-constructs.shtml

Read More

MySQL Transactional and Locking Statements

MySQL5.0 mendukung transaksi lokal dengan pernyataan seperti TRANSACTION START, SET AUTOCOMMIT, COMMIT dan ROLLBACK. Transaction berarti sekelompok SQL Konsolidasi yang menjalankan sebagai unit. Dan MySQL baik menjalankan semua pernyataan itu berhasil atau tidak mengeksekusi siapa pun. Hal ini dapat dicapai dengan melakukan rollback dan. Ketika semua laporan dijalankan dengan sukses maka anda dapat melakukan itu untuk efek database permanen. Tetapi jika kesalahan telah terjadi maka Anda dapat memutar kembali untuk pembatalan itu.

TRANSACTION START, COMMIT dan ROLLBACK

Sintaks umum BEGIN TRANSACTION, melakukan dan rollback adalah:

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}
START TRANSACTION dan BEGIN digunakan memulai transaksi baru. Dan COMMIT digunakan untuk melakukan (yang disimpan) transaksi berjalan berarti yang membuat perubahan permanen. ROLLBACK digunakan untuk mengulang kembali transaksi berjalan berarti membatalkan perubahan. SET AUTOCOMMIT pernyataan digunakan untuk menonaktifkan atau mengaktifkan default autocommit modus untuk transaksi berjalan.

Kata kunci opsional PEKERJAAN digunakan untuk mendukung untuk COMMIT dan ROLLBACK. Dan chain dan RELEASE digunakan untuk mengontrol tambahan atas penyelesaian transaksi. The And Chain klausa digunakan untuk memulai sebuah transaksi baru ketika arus satu berakhir. Dan ini baru memiliki tingkat isolasi yang sama. Klausa RELEASE digunakan untuk memutuskan hubungan server dengan koneksi klien saat setelah mengakhiri transaksi berjalan. Secara default MySQL dalam modus autocommit. Ketika kita menggunakan mesin transaksi penyimpanan yang aman seperti BDB, InnoDB maka kita dapat menonaktifkan modus autocommit dengan menggunakan pernyataan berikut:

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.06 sec)

 Dengan menggunakan pernyataan di atas kita dapat menonaktifkan autocommit mode, maka kita harus menggunakan COMMIT untuk menyimpan perubahan dalam disk. Dan jika kita ingin mengabaikan perubahan maka kita bisa menggunakan ROLLBACK. Untuk menonaktifkan modus autocommit dengan serangkaian pernyataan tunggal maka kita bisa menggunakan pernyataan START TRANSACTION. Contoh:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE Emp SET Perks=Perks*1.03 WHERE Salary>15000;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> COMMIT;

Query OK, 0 rows affected (0.02 sec)

Pernyataan yang tidak dapat Rolled Kembali

Dalam MySQL beberapa pernyataan yang tersedia bahwa kita tidak dapat diperpanjang kembali ini termasuk DDL (Data Definition Language) pernyataan seperti membuat database, membuat tabel, drop database, tabel drop, mengubah tabel. Pastikan Anda transaksi tidak termasuk jenis ini laporan. Jika Anda telah mengeluarkan pernyataan DDL awal transaksi dan laporan lain gagal maka anda tidak dapat bergulir kembali efek penuh transaksi dengan menerbitkan pernyataan ROLLBACK.

SAVEPOINT dan ROLLBACK ATAS SAVEPOINT

Sintaks umum SAVEPOINT dan ROLLBACK TO SAVEPOINT adalah:
SAVEPOINT savepoint_name
ROLLBACK [KERJA] ATAS savepoint_name SAVEPOINT
RELEASE SAVEPOINT savepoint_name

SAVEPOINT pernyataan digunakan untuk menetapkan savepoint transaksi bernama dengan nama apapun. The ROLLBACK ATAS SAVEPOINT pernyataan digunakan untuk memutar kembali transaksi untuk savepoint bernama. Berarti modifikasi pada baris yang kami buat dalam transaksi berjalan setelah savepoint, kita modifikasi ini digulung kembali. Kita dapat menghapus savepoint bernama dari set savepoints transaksi saat ini dengan menggunakan SAVEPOINT RELEASE. Contoh:

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 698   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.46 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE Emp
    -> SET Salary=Salary*1.03 WHERE Salary>=10000;
Query OK, 7 rows affected (0.10 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SAVEPOINT sve_point;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Emp VALUES(10,'Chandan','Delhi','Designer',20000,965);
Query OK, 1 row affected (0.03 sec)

mysql> ROLLBACK TO SAVEPOINT sve_point;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10609  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10609  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 15914  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 21218  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16480  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10300  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 20600  | 698   |
| 9   | Rajesh  | Delhi    | Developer         | 15000  | 965   |
+-----+---------+----------+-------------------+--------+-------+
8 rows in set (0.02 sec)

LOCK TABLES dan UNLOCK TABLES
Sintaks umum untuk mengunci dan membuka tabel adalah:LOCK TABLES tbl_name [AS alias] {READ [LOKAL] | [LOW_PRIORITY] WRITE} [, tbl_name [AS] alias {BACA [LOKAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES
 
Para LOCK TABLES digunakan untuk mengunci tabel dasar untuk thread ini. Jika ada thread lain mengunci tabel maka tabel ini diblokir sampai semua kunci dapat diperoleh. The UNLOCK TABLES digunakan untuk secara eksplisit melepaskan kunci yang dipegang oleh thread ini. Thread ini secara implisit membuka semua tabel jika isu-isu lain LOCK TABLES atau jika koneksi ke server ditutup. Setelah mendapatkan kunci global baca kita juga bisa menggunakan UNLOCK TABLE dengan FLUSH TABLES WITH READ LOCK untuk melepaskan kunci. Tetapi untuk menggunakan LOCK TABLES Anda memerlukan LOCK TABLES dan hak SELECT untuk tabel yang terlibat.
Sebuah mengunci tabel digunakan untuk melindungi hanya terhadap tidak pantas menulis atau dibaca oleh klien lain. Jika klien ada memegang kunci bahkan kunci membaca maka client dapat melakukan operasi tingkat tabel seperti DROP TABLE. Tapi operasi memotong tidak dapat dilakukan karena mereka tidak transaksi aman.
Penggunaan TABEL LOCK dengan tabel transaksional:

    * Seperti yang kita diskusikan LOCK TABLES tidak transaksi aman dan secara implisit melakukan melakukan operasi pada setiap transaksi aktif sebelum melakukan untuk mengunci meja. Dan mulai melakukan transaksi secara implisit suatu TABEL UNLOCK.
    * Untuk menggunakan LOCK TABLES dengan tabel transaksional seperti InnoDB, kita harus menetapkan AUTOCOMMIT = 0 dan kita tidak menyebutnya TABEL UNLOCK sampai kita komit transaksional secara eksplisit. Jika kita menelepon LOCK TABLES kemudian InnoDB internal membawanya kunci meja sendiri dan MySQL juga mengambil kunci tabel sendiri. Pada berikutnya melakukan InnoDB dirilis mengunci tabel tetapi untuk melepaskan MySQL kita harus memanggil UNLOCK TABLES. Namun jika kita AUTOCOMMIT = 1 maka InnoDB dirilis kunci meja segera setelah panggilan LOCK TABLES itu sebabnya kebuntuan dengan mudah dapat terjadi.
    * ROLLBACK tidak dapat melepaskan MySQL non kunci tabel transaksional.
    * FLUSH TABLES DENGAN READ LOCK digunakan untuk mendapatkan kunci global daripada kunci tabel.
Ketika kita menggunakan LOCK TABLES maka kita harus mengunci semua tabel yang kita gunakan dalam laporan kami. LOCK TABLES tidak bisa mengunci pandangan itulah sebabnya jika kita menggunakan tampilan apapun maka kita harus mengunci semua tabel dasar yang pandangan-pandangan ini tergantung. Ketika kita memperoleh kunci dengan pernyataan TABEL LOCK maka kita tidak bisa mengakses tabel yang tidak terkunci. Tetapi jika anda menggunakan alias dalam hal ini Anda harus mendapatkan kunci untuk masing-masing alias terpisah. Contoh:

mysql> LOCK TABLE Emp AS eealias WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
ERROR 1100 (HY000): Table 'Emp' was not locked with LOCK TABLES
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,965);
Query OK, 1 row affected (0.04 sec)

mysql> LOCK TABLE Emp AS ee READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Emp;
ERROR 1100 (HY000): Table 'Emp' was not locked with LOCK TABLES
mysql> SELECT * FROM Employee;
ERROR 1100 (HY000): Table 'Employee' was not locked with LOCK TABLES
mysql> SELECT * FROM Emp AS ee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10927  | 879   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10927  | 879   |
| 3   | Chandan | Banglore | Team Leader       | 16391  | 1060  |
| 5   | Tapan   | Pune     | Developer         | 21855  | 1178  |
| 6   | Amar    | Chennai  | Developer         | 16974  | 1193  |
| 7   | Santosh | Delhi    | Designer          | 10609  | 891   |
| 8   | Suman   | Pune     | Web Designer      | 21218  | 698   |
| 9   | Rajesh  | Delhi    | Developer         | 15000  | 965   |
+-----+---------+----------+-------------------+--------+-------+

8 rows in set (0.00 sec)

WRITE locks memiliki prioritas lebih tinggi untuk READ kunci itu berarti update diproses secepat mungkin. Berarti jika thread ada mendapatkan READ LOCK dan permintaan lain thread untuk kunci berikut READ WRITE maka permintaan kunci harus menunggu sampai thread WRITE telah merilis kunci. Untuk mendapatkan READ kunci ke benang lain kita dapat menggunakan LOW_PRIORITY TULIS kunci.

Penguncian Tabel adalah kebuntuan gratis. Anda harus berhati-hati jika Anda menggunakan sebuah LOW_PRIORITY MENULIS kunci, yang berarti hanya MySQL sedang menunggu kunci tertentu thi sampai ada benang yang tersedia yang ingin READ kunci. Setiap thread telah memperoleh ijin WRITE dan yang menunggu untuk mendapatkan kunci untuk tabel berikutnya dalam daftar tabel kunci maka semua thread lain harus menunggu untuk mengunci WRITE akan dirilis.

Jika kita ingin mengakhiri sebuah thread yang sedang menunggu untuk kunci tabel maka kita dapat menggunakan pernyataan KILL. Sintaks umum dari pernyataan KILL adalah:
         KILL [CONNECTION| QUERY] thread_id

Setiap koneksi untuk mysqld berjalan di thread terpisah. Dengan pernyataan processlist SHOW kita dapat melihat benang yang sedang berjalan dan kita dapat membunuh thread dengan pernyataan KILL. HUBUNGAN KILL sama seperti pernyataan KILL dengan modifier no. Hal ini digunakan untuk mengakhiri koneksi yang terkait dengan thread_id diberikan. KILL QUERY digunakan untuk menghentikan pernyataan bahwa koneksi yang sedang dijalankan tetapi meninggalkan koneksi itu sendiri utuh. Untuk mendapatkan daftar semua thread Anda membutuhkan hak istimewa PROSES dan untuk membunuh semua benang dan pernyataan maka Anda perlu hak istimewa SUPER lain Anda dapat melihat dan membunuh hanya benang Anda dan pernyataan. Contoh:

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 9
   User: root
   Host: localhost:1786
     db: employee
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
1 row in set (0.00 sec)

mysql> KILL 9;
ERROR 2013 (HY000): Lost connection to MySQL server during query

   
Jika Anda menggunakan pernyataan INSERT DELAYED ke tabel apapun maka Anda tidak perlu untuk mengunci tabel yang karena di sini pernyataan INSERT dilakukan oleh thread terpisah.

SET TRANSACTION

Sintaks umum SET TRANSACTION adalah:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SET TRANSACTION digunakan untuk mengatur tingkat transaksi isolasi untuk global atau sesi saat ini atau untuk transaksi berikutnya. Ketika kita menggunakan kata kunci GLOBAL maka menentukan tingkat standar transaksi global tetapi Anda membutuhkan hak istimewa SUPER untuk melakukan hal ini. Dan kata kunci SESI digunakan untuk mengatur tingkat standar transaksi untuk semua transaksi masa depan yang dilakukan pada koneksi saat ini.

     * Repeatable READ - Ini adalah tingkat isolasi default untuk tabel InnoDB. Dalam hal ini, semua membaca konsisten dalam transaksi.
     * READ UNCOMMITTED - Hal ini digunakan, dimana dalam satu query transaksi dipengaruhi oleh perubahan yang tidak mengikat dalam transaksi lain.
     * READ COMMITTED - Jika kita menggunakan pengaturan ini maka pembaruan komitmen yang terlihat dalam transaksi lain juga.
     * Serializable - Dalam pembaruan setelah tidak diperbolehkan dalam transaksi lain.

REPEATABLE READ Contoh:

Connection 1
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL
    -> REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql>  BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Emp;
+-----+-------+-------+-------------+--------+-------+
| Eid | Ename | City  | Designation | Salary | Perks |
+-----+-------+-------+-------------+--------+-------+
| 1   | Rahul | Delhi | Manager     | 10927  | 879   |
+-----+-------+-------+-------------+--------+-------+
1 row in set (0.00 sec)

     

Connection 2 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Emp VALUES(2,'Suman','Mumbai','Designer',20000,865); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.00 sec)

    

Connection 1 mysql> SELECT * FROM Emp; +-----+-------+-------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+-------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | +-----+-------+-------+-------------+--------+-------+ 1 row in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.01 sec)

READ UNCOMMITTED Example :

Connection 1 mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL -> READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Emp VALUES(3,'Chandan','Delhi','G Manager',30000,999); Query OK, 1 row affected (0.00 sec)

      

Connection 2mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.01 sec)

    

Connection 1 mysql> ROLLBACK; Query OK, 0 rows affected (0.08 sec)

    

Connection 2 mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.01 sec)

READ COMMITTED Example :

Connection 1 mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL -> READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

    

Connection 2 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Emp VALUES(3,'Chandan','Delhi','G Manager',25000,986); Query OK, 1 row affected (0.00 sec)

    

Connection 1 mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.00 sec)

       

Connection 2 mysql> COMMIT; Query OK, 0 rows affected (0.03 sec)

    

Connection 1 mysql> SELECT * FROM Emp; +-----+---------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | | 3 | Chandan | Delhi | G Manager | 25000 | 986 | +-----+---------+--------+-------------+--------+-------+ 3 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

SERIALIZABLE Example :

Connection 1 mysql> SET TRANSACTION ISOLATION LEVEL -> SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Emp; +-----+---------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10927 | 879 | | 2 | Suman | Mumbai | Designer | 20000 | 865 | | 3 | Chandan | Delhi | G Manager | 25000 | 986 | +-----+---------+--------+-------------+--------+-------+ 3 rows in set (0.00 sec)

    

Connection 2 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE Emp SET Salary=Salary*1.03;

Pada koneksi pertama kita mengeksekusi statemen SELECT bahwa sebabnya UPDATE terkunci.    
       

Connection 1mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

     

Connection 2
Query OK, 3 rows affected (9.12 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM Emp;
+-----+---------+--------+-------------+--------+-------+
| Eid | Ename   | City   | Designation | Salary | Perks |
+-----+---------+--------+-------------+--------+-------+
| 1   | Rahul   | Delhi  | Manager     | 11255  | 879   |
| 2   | Suman   | Mumbai | Designer    | 20600  | 865   |
| 3   | Chandan | Delhi  | G Manager   | 25750  | 986   |
+-----+---------+--------+-------------+--------+-------+
3 rows in set (0.00 sec)

  

sumber : http://www.roseindia.net/mysql/mysql5/mysql-transactional-and-locking.shtml 

Read More