Tutorial Addendum on SQL - Locks And Deadlocks
| |
We all understand that assorted locks in assorted sessions could couldcause deadlocks.
Let s see if we can make a deadlock in MySQL amid two sessions:
mysql> Alert >;
>DROP TABLE IF EXISTS User;
>CREATE TABLE User (ID INT
> PRIMARY KEY, Name CHAR(8))
> ENGINE=InnoDB;
>INSERT INTO User Ethics (2,
> bill );
>LOCK TABLES User WRITE;
c:>mysqlinmysql test
mysql> Alert >;
>DROP TABLE IF EXISTS System;
>CREATE TABLE Arrangement (ID INT
> PRIMARY KEY, Name CHAR(8));
>INSERT INTO Arrangement Ethics (2,
> atramentous );
>SELECT * FROM User;
+----+------+
| 2 | bill |
+----+------+
>LOCK TABLE Arrangement WRITE;
>SELECT * FROM User;
Absurdity Table user was not bound
with LOCK TABLES
MySQL s behavior on my "SELECT * FROM User" is interesting. It errored out
instead of putting the affair into a cat-and-mouse status. I anticipate it is accomplishing this
in adjustment to abstain the achievability of deadlocks.
After some added tests, I begin that MySQL s aphorism is actual conservative:
If a affair is using LOCK TABLES, it can alone plan on the bound tables.
Row Akin Locks in MySQL
In MySQL, row akin locks appear around aural transactions.
Here is an example:
c:>mysqlinmysql test
mysql> Alert >;
>DROP TABLE IF EXISTS User;
>CREATE TABLE User (ID INT
> PRIMARY KEY, Name CHAR(8))
> ENGINE=InnoDB;
>INSERT INTO User Ethics (2,
> bill );
>SET TRANSACTION Abreast Akin
> Apprehend UNCOMMITTED;
>START TRANSACTION;
>UPDATE User SET Name= bob
> Area ID= 2 ;
c:>mysqlinmysql test
mysql> Alert >;
>INSERT INTO User Ethics (3,
> jack );
>SELECT * FROM User;
+----+------+
| 2 | bill |
| 3 | jack |
+----+------+
>UPDATE User SET Name= amber
> Area ID= 2 ;
(waiting for the lock to release)
>COMMIT;
(insert done)
The achievement seems to be cogent me that:
- Row akin locking accessible with accumulator engine InnoDB.
- UPDATE statements around make Apprehend locks on afflicted rows in a transaction.
- Locks around created by statements in a transaction will be appear
when the transaction ends.
Another way to lock rows is to the "LOCK IN Allotment MODE" advantage in Baddest statements:
c:>mysqlinmysql test
mysql> Alert >;
>DROP TABLE IF EXISTS User;
>CREATE TABLE User (ID INT
> PRIMARY KEY, Name CHAR(8))
> ENGINE=InnoDB;
>INSERT INTO User Ethics (2,
> bill );
>SET TRANSACTION Abreast Akin
> Apprehend UNCOMMITTED;
>START TRANSACTION;
>SELECT * FROM User Area ID= 2
> LOCK IN Allotment MODE;
c:>mysqlinmysql test
mysql> Alert >;
>INSERT INTO User Ethics (3,
> jack );
>SELECT * FROM User;
+----+------+
| 2 | bill |
| 3 | jack |
+----+------+
>UPDATE User SET Name= amber
> Area ID= 2 ;
(waiting for the lock to release)
>COMMIT;
(insert done)
|
table, insert, select, locks, transaction, mysql, create, values, prompt, mysqlinmysql, level, engine, deadlocks, update, system, primary, exists, innodb, statements, implicitly, tables, , insert into, user values, primary key, mysql prompt, engine innodb, int primary, name char, key name, prompt drop, table user, drop table, create table, row level, update user, user set, test mysql, mysqlinmysql test, innodb insert, user create, exists user, insert into user, key name char, prompt drop table, mysqlinmysql test mysql, test mysql prompt, innodb insert into, engine innodb insert, exists user create, user create table, create table user, mysql prompt insert, uncommitted start transaction, prompt insert into, read uncommitted start, commit insert done, release commit insert, name brown where, row level locks, mysqlinmysql testmysql prompt, mysql prompt drop, testmysql prompt drop, transaction isolation level, isolation level read, level read uncommitted, |
Text link code :
Hyper link code:
Also see ...
This affiliate describes:What s in MySQL?How to use the SQL applicant apparatus mysql.How to dump data into files.How to amount data from fil
To assassinate the statements in hello.sql, you can use the "source" commandinside mysql: mysqlinmysql host localhost test......mysql antecedent hello.sqlQuery OK, 0 rows affli
This affiliate describes:Expressions.Arithmetic operations.Predicate operations.Date and time operations.Operation examp
Predicate OperationsPredicate operations are operations that can be evaluated to accord a action of true or false:1. "Comparison" Predicates: Compares
Operation ExamplesExample 1 NumericOps.sql NumericOps.sql Absorb (c) 1999 by Dr. Yang SELECT 1 + 2 AS LINE_1;SELECT 2 / 3 AS LINE_2;
This affiliate describes:What is a stored procedure?How to make a stored procedure.How to canyon data through parameters.
Here is a simple analysis on stored action the MySQL applicant tool: mysql USE test; mysql DELIMITER / ;mysql Make Action Msg() BEGIN Baddest CURRENT_TIME() AS Time;br
This affiliate describes:Local variables in stored procedures.Execution breeze ascendancy statements.Cursor accompanying statements.br
Below is a sample cipher that uses a while bend to admit assorted rows into a table: ProcedureLoop.sql Absorb (c) 2004 by Dr. Yang DROP2 DATABASE IF EXISTS HyTest;CREATE DATABASE H
CursorsA cursor is a data blazon that represents a affiliation handle to the achievement table of a baddest statement. A cursor is actual agnate to a book handle in some additional programm