SQL transaktsions

сновные концепции транзакции описываются аббревиатурой ACID –

• Atomicity – Atomic
• Consistency – Consistency
• Isolation – Isolation
• Durability – Durability

The following commands are used to manage transactions:

• COMMIT
  Saves changes
• ROLLBACK
  Rolls back (undoes) changes
• SAVEPOINT
  Creates a point to which a group of transactions can be rolled back
• SET TRANSACTION
  Places the name of the transaction.

create table T(
id int not null primary key,
s varchar(40),
si SMALLINT);


insert into T(id, s) values (1, 'first');
insert into T(id, s) values (2, 'second');
insert into T(id, s) values (3, 'third');

SELECT * from T;

--transaktsiooni loomine
begin transaction;
insert into T(id, s) values (4, 'fourth');
SELECT * from T;
--tegevuse tagasi võtmine
Rollback;
Select * from T;
--loome transaktsiooni, mis võtab tagasi kustutatud kirjed
Begin transaction;
Delete from T where id>1;
select * from T;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

XAMPP START = BEGIN

1.MySQL – Run example from next page

Руководство по SQL. Транзакции.

developers table creating

adding data to a table

saving changes in the table

I will cancel this change

I delete the fifth ID

I will cancel this change

Task for five:

create table

create table tblPhysicalAddress(
Addressid int not null primary key,
EmployeeNumber int,
HouseNumber varchar(20),
StreetAddress varchar(20),
City varchar(20),
PostalCode varchar(20));Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

create transaction procedure, tries to update the cities fields in both tables during the transaction

create  procedure spUpdateAddress
as
Begin
	Begin try
		Begin Transaction
			Update tblPhysicalAddress set City='LONDON'
			where Addressid = 1 and EmployeeNumber = 101

			Update tblPhysicalAddress set City = 'LONDON LONDON'
			where Addressid = 1 and EmployeeNumber = 101
		Commit Transaction
		Print 'Transaction Rolled BACK'
	END TRY
	Begin Catch
		Rollback Transaction
		print 'Transaction Rolled Back'
	END Catch
ENDCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

BEFORE PROCEDURE

AFTER PROCEDURE