What are the SQL transactions? are a set of SQL statements that have the quality to run as a single unit that is running or not running all or none.
A tables
able to support transactions are called
ACID (Atomicity, Consistency, Isolation
, Durability)
English (Atomicity, Consistency, Isolation & Durability)
Atomicity: => Something that can not be divided, I mean all queries are treated as one and they only run when all are correct in the event of a mistake does not run anything.
Consistency: => This is linked to referential integrity, ie can only be write valid data in compliance with the declared data types and referential integrity.
Isolation: => Each transaction is executed independently of other transactions or rather one after another, after these transactions are executed (successful or wrong) the changes are visible to other users. Durability: =>
When a transaction completes successfully the changes become permanent.
-
-
Example
-
Let the following example how important it may be a transaction.
-
Suppose that two vendors store availability consult the LCD and only have one is stock (as these are hot) to make a sale and one is a TV in stock.
only with SQL
First
Vendor 1
: SELECT stock FROM TV;
Seller 2: TV
SELECT FROM stock;
Seller
1: UPDATE
TVs SET stock = stock -1;
Seller 2: UPDATE
TV SET stock = stock -1;
SELECT First to make each vendor will see on your screen that is available 1 television.
The seller 1
sell the TV leaving the stock to 0, then the seller 2
does not know that the TV has been sold and will sell TV (think there is stock) and the stock will be -1 and the latter has been sold by the seller 1
. If real life was a customer will have to wait longer than normal to reach your TV. Now same Transactions Seller 1: START TRANSACTION; Vendor 1
: SELECT stock FROM TV;
Seller 1: UPDATE
TV SET stock = stock -1;
Seller
1: COMMIT , (running)
Seller 2:
START TRANSACTION;
Seller 2:
SELECT stock FROM TV;
Seller 2: ROLLBACK; (out of the transaction without doing anything more)
Seller 2: Tip client not availability and asks if you want to buy and wait a few days until there is stock again.
As we have seen the operations were computer allows data consistency after the first vendor to finish the operation performed seller 2 queries. Example 2 (Something more work)
This example shows how to perform operations in a transaction have 1 table called departments with the following structure and records
CREATE TABLE `departments` (
CODIGODEP `` INTEGER (11) NOT NULL DEFAULT '0 ', `NOMBREDEP
` VARCHAR (100),
BUDGET `` INTEGER (11),
PRIMARY KEY (`CODIGODEP`)
) ENGINE = InnoDB INSERT INTO `departments` (`CODIGODEP`, `NOMBREDEP`, `Budget`) VALUES (1, 'Computer', 300000), (2, 'Personal', 200000), (11, 'Quality', 150 000), (13, 'Security', 450,000), (56, 'HR', 900,000), (77, 'Administration' 1500000);
and now create a new departamentos2 table and assign 30% of the money from the budget of each department departamentos2 table, ie departmental budget subtract 30% of its budget and assign to departments departamentos2 table. (The structures are equal and incuse keep the same codes and names of departments)
departamentos2 `CREATE TABLE` (`CODIGODEP
` INTEGER (11) NOT NULL DEFAULT '0 ', ` NOMBREDEP `VARCHAR (100), BUDGET` `INTEGER (11),
PRIMARY KEY (` CODIGODEP `)
) ENGINE = InnoDB
now
transactions
START TRANSACTION;
departamentos2 insert into `select`
codigodep, nombredep, budget departments * `from` 0.30; September update departments budget = budget - (budget * 0.30 ) COMMIT;
The result has been a table of all records of departments 1 and budgets of the two tables have been properly modified by adding and subtracting 30% respectively.
This will prevent any other user is modifying the table when performing the operation departments Jlara
