The Database Should Handle 100,000 Read Transactions
SQL - Transactions
A transaction is a unit of piece of work that is performed confronting a database. Transactions are units or sequences of piece of work accomplished in a logical order, whether in a manual way past a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, so you are performing a transaction on that tabular array. It is of import to control these transactions to ensure the data integrity and to handle database errors.
Practically, you will society many SQL queries into a group and you volition execute all of them together as a role of a transaction.
Properties of Transactions
Transactions have the following iv standard properties, normally referred to by the acronym Acid.
-
Atomicity − ensures that all operations within the piece of work unit of measurement are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
-
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
-
Isolation − enables transactions to operate independently of and transparent to each other.
-
Durability − ensures that the effect or result of a committed transaction persists in case of a system failure.
Transaction Control
The following commands are used to control transactions.
-
COMMIT − to save the changes.
-
ROLLBACK − to roll back the changes.
-
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
-
SET TRANSACTION − Places a proper noun on a transaction.
Transactional Control Commands
Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.
The COMMIT Control
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
The COMMIT command is the transactional control used to save changes invoked past a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows.
COMMIT;
Example
Consider the CUSTOMERS table having the post-obit records −
+----+----------+-----+-----------+----------+ | ID | Proper name | Age | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | iii | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Bombay | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | half-dozen | Komal | 22 | MP | 4500.00 | | vii | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Following is an example which would delete those records from the tabular array which accept historic period = 25 and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS WHERE Historic period = 25; SQL> COMMIT;
Thus, two rows from the tabular array would be deleted and the SELECT argument would produce the following result.
+----+----------+-----+-----------+----------+ | ID | Name | Age | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | i | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | five | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | seven | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The ROLLBACK Command
The ROLLBACK command is the transactional control used to undo transactions that take not already been saved to the database. This command can only exist used to undo transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for a ROLLBACK command is as follows −
ROLLBACK;
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+ | ID | Name | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | ane | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Bombay | 6500.00 | | five | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | seven | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Following is an instance, which would delete those records from the table which take the historic period = 25 and and then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS WHERE Historic period = 25; SQL> ROLLBACK;
Thus, the delete operation would not bear upon the table and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+ | ID | Proper noun | AGE | Address | SALARY | +----+----------+-----+-----------+----------+ | i | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | four | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | seven | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The SAVEPOINT Control
A SAVEPOINT is a point in a transaction when you tin can ringlet the transaction back to a sure signal without rolling back the entire transaction.
The syntax for a SAVEPOINT command is as shown below.
SAVEPOINT SAVEPOINT_NAME;
This control serves only in the creation of a SAVEPOINT amongst all the transactional statements. The ROLLBACK command is used to disengage a group of transactions.
The syntax for rolling dorsum to a SAVEPOINT is every bit shown below.
ROLLBACK TO SAVEPOINT_NAME;
Following is an instance where you plan to delete the three unlike records from the CUSTOMERS table. You lot want to create a SAVEPOINT before each delete, so that you tin ROLLBACK to whatever SAVEPOINT at whatever time to return the advisable data to its original state.
Example
Consider the CUSTOMERS table having the following records.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | Accost | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | half dozen | Komal | 22 | MP | 4500.00 | | vii | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The following code cake contains the series of operations.
SQL> SAVEPOINT SP1; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=i; 1 row deleted. SQL> SAVEPOINT SP2; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=two; i row deleted. SQL> SAVEPOINT SP3; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=3; 1 row deleted.
At present that the three deletions accept taken identify, allow u.s.a. assume that yous take inverse your mind and decided to ROLLBACK to the SAVEPOINT that you identified equally SP2. Considering SP2 was created afterwards the first deletion, the last two deletions are undone −
SQL> ROLLBACK TO SP2; Rollback complete.
Discover that simply the first deletion took place since you rolled back to SP2.
SQL> SELECT * FROM CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | Proper noun | AGE | Address | Salary | +----+----------+-----+-----------+----------+ | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | four | Chaitali | 25 | Mumbai | 6500.00 | | five | Hardik | 27 | Bhopal | 8500.00 | | six | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ 6 rows selected.
The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that y'all accept created.
The syntax for a RELEASE SAVEPOINT command is every bit follows.
RELEASE SAVEPOINT SAVEPOINT_NAME;
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK control to undo transactions performed since the concluding SAVEPOINT.
The Set up TRANSACTION Command
The SET TRANSACTION command can exist used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you lot can specify a transaction to exist read only or read write.
The syntax for a Ready TRANSACTION command is as follows.
Gear up TRANSACTION [ READ WRITE | READ ONLY ];
Useful Video Courses
Video
Video
Video
Video
Video
Video
Source: https://www.tutorialspoint.com/sql/sql-transactions.htm
0 Response to "The Database Should Handle 100,000 Read Transactions"
Post a Comment