Spot Dangers Worksheet

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.

2. Example

3. Let the following example how important it may be a transaction.

4. 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