Ишдан мақсади: Маъулмотлар базаси учун оддий транзакция яратиш ва тестдан ўтказиш. Транзакциянинг моделлари, транзакция орқали сақлаш моделлари, қўшилган транзакциялар ва уларни ишлатиш принциялари

4-амалий машғулот. Университет маълумотлар базасида транзакцияларни амалга ошириш ва қайта ишлаш.

Ишдан мақсади: Маъулмотлар базаси учун оддий транзакция яратиш ва тестдан ўтказиш. Транзакциянинг моделлари, транзакция орқали сақлаш моделлари, қўшилган транзакциялар ва уларни ишлатиш принциялари, транзакция билан ишловчи SQL операторлари билан ишлаш.


Калит сўзлар: транзакция, SQL, амаллар кетма – келиги, транзакция хусусиятлари, сақлаш структураси


Масаланинг қўйилиши: Университет маълумотлар базасида транзакцияларни амалга ошириш ва қайта ишлаш

Ишни бажариш учун намуна

BEGIN TRANSACTION
INSERT СОТРУДНИКИ (TAB) VALUES (5) INSERT ОТРАБОТАНО (TAB) VALUES (5) IF @@ERROR = 0
COMMIT ELSE ROLLBACK

CREATE PROCEDURE TRANZ
AS BEGIN
BEGIN TRANSACTION
INSERT СОТРУДНИКИ (TAB) VALUES (11)
INSERT ЧАСЫ (TAB) VALUES (11)
IF @@ERROR = 0 COMMIT
ELSE ROLLBACK END


private const int DefaultRetryCount = 6;
private const int DeadlockErrorNumber = 1205; private const int LockingErrorNumber = 1222; private const int UpdateConflictErrorNumber = 3960; private void RetryOnDeadlock(
Action action, int retryCount = DefaultRetryCount)
{ if (action == null)
throw new ArgumentNullException("action");
var attemptNumber = 1;
while (true) { var dataContext = CreateDataContext();
try { action(dataContext); break; }
catch (SqlException exception)
{
if(!exception.Errors.Cast().Any(error =>
(error.Number == DeadlockErrorNumber) || (error.Number == LockingErrorNumber) || (error.Number == UpdateConflictErrorNumber)))
{ throw; }
else if (attemptNumber == retryCount + 1) { throw; } }
finally { dataContext.Dispose(); } attemptNumber++; }
}

BEGIN TRAN

UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176'
UPDATE authors SET au_fname = 'Marg' WHERE au_id = '213-46-8915' COMMIT TRAN
BEGIN TRAN
UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176' UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence' IF @@ROWCOUNT = 5 COMMIT TRAN
ELSE
ROLLBACK TRAN

Create Proc TranTest1 AS BEGIN TRAN

INSERT INTO [authors]([au_id], [au_lname], [au_fname], [phone], [contract]) VALUES ('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)
UPDATE authors SET au_fname = 'Johnzzz' WHERE au_id = '172-32-1176' COMMIT TRAN GO

Create Proc TranTest2 AS BEGIN TRAN

INSERT INTO [authors]([au_id], [au_lname], [au_fname], [phone], [contract]) VALUES ('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return 10 END
UPDATE authors SET au_fname = 'Johnzzz' WHERE au_id = '172-32-1176' IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return 11 END
COMMIT TRAN GO






USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
DELETE sales BEGIN TRAN nested
SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 2
DELETE titleauthor COMMIT TRAN nested
-- Does nothing except decrement the value of @@TRANCOUNT

SELECT 'After COMMIT TRAN nested', @@TRANCOUNT

-- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0

-- because ROLLBACK TRAN always rolls back all transactions and sets





private static void ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

SqlCommand command = connection.CreateCommand(); SqlTransaction transaction;

// Start a local transaction.

transaction = connection.BeginTransaction("SampleTransaction");

// Must assign both transaction object and connection

// to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction;

try
{

command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery(); command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery();

// Attempt to commit the transaction. transaction.Commit();

Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message);

// Attempt to roll back the transaction.

try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}

using (IDbTransaction tran = conn.BeginTransaction()) { try {
// your code tran.Commit();
} catch { tran.Rollback(); throw;
}
}

using(TransactionScope tran = new TransactionScope()) { CallAMethodThatDoesSomeWork(); CallAMethodThatDoesSomeMoreWork(); tran.Complete();
}

protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection connection1 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integ rated Security=True;User Instance=True"))
{
connection1.Open();

// Start a local transaction.

SqlTransaction sqlTran = connection1.BeginTransaction();

// Enlist a command in the current transaction. SqlCommand command = connection1.CreateCommand(); command.Transaction = sqlTran;

}

Тавсия қилинадиган адабиѐтлар

1. 
   Silberschatz, Abraham.Database system concepts / Abraham Silberschatz.6th ed.p. cm. 2011. ISBN 978-0-07-352332-3
   
2. 
   N. Walsh et al.―XQuery 1.0 and XPath 2.0 Data Model‖.http://www.w3.org/TR/xpath-datamodel. currently a W3C Recommendation(2007).
   
3. 
   SQL/XML.―ISO/IEC 9075-14:2003, Information Technology:Database languages: SQL.Part 14: XML-Related Specifications (SQL/XML)‖(2004)
   
4. 
   J. Han and M. Kamber,Data Mining: Concepts and Techniques, Morgan Kaufmann (2000)