Look at the database (4) Transaction again

Source: Internet
Author: User
What is a transaction? Simply put, it is what you do or what you do. A term refers to a program execution unit that accesses and may update various data items in the database. In a database, a transaction can be an SQL statement, a group of SQL statements, or the entire program. For example, transfer money from account A to account B. This process requires two steps

What is a transaction? Simply put, it is what you do or what you do. A term refers to a program execution unit that accesses and may update various data items in the database. In a database, a transaction can be an SQL statement, a group of SQL statements, or the entire program. For example, transfer money from account A to account B. This process requires two steps

What is a transaction? Simply put, it is what you do or what you do. A term refers to a program execution unit that accesses and may update various data items in the database.

In a database, a transaction can be an SQL statement, a group of SQL statements, or the entire program.

For example, transfer money from account A to account B. This process requires two steps: 1) deduct money from account A; 2) Add the corresponding amount of money to account B. These two steps are indispensable. If an error occurs, the consequences are unimaginable. What should we do? This leads to the usefulness of transactions.

The SQL statement is as follows. If no error occurs, the transaction transfer is successfully submitted. If an error occurs, perform rollback. The database design is also very simple, a money table, two fields bankName (account name) and totalMoney (total assets ).

Begin trybegin tran -- start transaction update money set totalMoney = totalMoney-100 where bankName = 'A' -- A account minus 100 update money set totalMoney = totalMoney + 100 where bankName = 'B' -- B account add 100 commit tran -- commit transaction End trybegin catchrollback tran -- transaction rollback end catch
The transfer is also used as an example to transfer money from A to B.

The Code is as follows:

Private void button1_Click (object sender, EventArgs e) {SqlTransaction sqlTrans = null; SqlConnection con = new SqlConnection ("server = .; database = bank; uid = sa; pwd = 123456; "); try {con. open (); // start the transaction sqlTrans = con. beginTransaction (); SqlCommand cmd = new SqlCommand ("", con, sqlTrans); cmd. commandTimeout = 120; cmd. commandType = System. data. commandType. text; string cutA = "update money set totalMoney = TotalMoney-@ count where bankName = 'A' "; string addB =" update money set totalMoney = totalMoney + @ count where bankName = 'B '"; // assign SqlParameter paras = new SqlParameter ("@ count", txtMoney. text); cmd. parameters. add (paras); cmd. commandText = cutA; cmd. executeNonQuery (); cmd. commandText = addB; cmd. executeNonQuery (); // throw new Exception ("test exception. the transaction must rollback "); used to test transaction rollback // commit SqlTrans. commit ();} catch (Exception ex) {// transaction rollback sqlTrans. rollback (); Console. write (ex. message);} finally {if (con. state! = System. Data. ConnectionState. Closed) con. Close () ;}console. ReadLine ();}
Now let's take a look at the transaction rollback situation: Restore the code that has been viewed above to normal and run it again. The two operations are not executed to reduce the money of account A and increase the money of Account B. Even if an error occurs, no more serious consequences will be caused, it only restores the program to the status before execution.

After reading the example above, we can better understand the attributes of transactions, including atomicity, consistency, isolation, and durability. These four attributes are generally called ACID properties.
1) atomicity. This indicates that the transaction is an inseparable unit of work, and all operations included in the transaction are either done or not done. This is also the core.

2) consistency. Transactions must change the database from one consistent state to another. This is very similar to atomicity.

3) isolation. The execution of a transaction cannot be disturbed by other transactions.
4) durability persistence. Once a transaction is committed, its changes to the data in the database should be permanent. If the transfer is successful, the data will be updated to the database.

Feeling: transaction. This term has long been heard. It is difficult to understand and practice it until now. As a result, similar content is always walking away and many learning opportunities are missed. The sooner you get to know it, the more opportunities you will have to practice.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.