如何解决数据库并发问题? [英] How to solve database cuncurrency problem?

问题描述

我正在使用一个在线发布的asp.net页面.此页面插入一个记录表.当同时从两个不同的位置访问此页面以保存数据时会出现问题.也是交易.

代码段如下:

I am using a asp.net page which is published online.This page inserts a records a table.The problem arises when this page is accessed from two different locations at the same time for saving data.Well, I am using a transaction too.

The Code Snippet is given below:

mConn = new OdbcConnection();
       mConn.ConnectionString = Global.mconnectionstr;
       mConn.Open();
       mTrans = mConn.BeginTransaction(IsolationLevel.Serializable);
       string mkey = "000000" + lib.FnGenAutoCode("MR", "RECURR", "PAY", 6, mTrans, (string)Session["COMPANYID"].ToString(), Session["BRANCHID"].ToString());
       MONEY_RECEIPT = "MR/" + mkey.Substring(Convert.ToInt16(mkey.Length - 6), (Convert.ToInt16(mkey.Length) - Convert.ToInt16(mkey.Length - 6)));
       if (ReadGrid())
       {
           int key = lib.FnGenAutoCode("TRN_COMMISSION_PAID", "TRN_COMMISSION_PAID", "TRN_COMMISSION_PAID", 5, mTrans, (string)Session["COMPANYID"].ToString(), Session["BRANCHID"].ToString());
           string fStrSql = " INSERT INTO [TRN_COMMISSION_PAID] VALUES (" +
          " '" + (string)Session["COMPANYID"].ToString() + "','" + Session["BRANCHID"].ToString() + "','" + key + "','" + adv_code + "','" + txtTotal.Text + "','" + DateTime.Now.Date.ToString("MM/dd/yyyy") + "'" +
          ",'" + MONEY_RECEIPT + "','" + tds_amt.ToString() + "','" + serv_amt.ToString() + "','" + project_amt.ToString() + "'" +
          " ,'" + payable_amt + "','" + Session["User"].ToString() + "','" + DateTime.Now.Date.ToString("MM/dd/yyyy") + "','','','I','" + txtLoan.Text + "')";
           if (lib.FnExecuteQuery(fStrSql, mTrans) && UpdateLoanAmount())
           {
               UserMsg = "Data saved successfully";
               lblstatus.Text = UserMsg;
               //PendingInstallMent(txtAdvisor.Text);
               clerfld();
               mTrans.Commit();
               string sql_adv = "select CODE from TRN_ADVISOR where REFCODE='" + txtAdvisor.Text + "'";
               string adv = lib.FnFetchText(sql_adv);

               Response.Write("<script>");
               Response.Write("window.open('CommissionVoucher.aspx?moneyreceipt=" + MONEY_RECEIPT +
                   "&adv=" + adv +
                   "&frmdate=" + lib.FnQuatationRps(txtFrmDate.Text) +
                   "&todate=" + lib.FnQuatationRps(txtToDate.Text) + "','_blank')");
               Response.Write("</script>");
           }
           else
           {
               lblstatus.Text = "Unable to Save record";
               mTrans.Rollback();
           }
           mTrans.Dispose();
           mConn.Close();
       }
       mTrans.Dispose();
       mConn.Close();

请帮助..

Please Help..

推荐答案

仔细阅读以下文档，您将获得一切...

[ http://www.developerfusion.com/article/84418 /concurrency-handling-techniques-in-adonet/]

Hi,
Read Following Doc Carefully You Will get All Things........

[ http://www.developerfusion.com/article/84418/concurrency-handling-techniques-in-adonet/]

您可以采取多种措施来改善并发问题.
1. IsolationLevel可序列化:避免除非重要的是确保最高级别的数据完整性.
在您的情况下，您正在插入数据(您可能不会更新同一记录)，并且您不需要Serializable(我将使用ReadCommitted).
2.将您的交易减少到最低限度(即:如果您需要计算包含查询的字符串，请在开始交易之前执行该操作).限制开放交易的时间可以帮助大量应用程序.
3.制定良好的维护计划.有时，碎片索引可能会影响并发问题(避免使用"SELECT * From Table")

这仅仅是开始，每个应用程序和数据库都有一套自己的规则来管理更好的并发性.

There are several things you can do to improve the concurrency issue.
1. IsolationLevel Serializable: to avoid unless it is important to ensure the highest level of data integrity.
In your case you are inserting data (you are not potentially updating the same record) and you do not need Serializable (i would use ReadCommitted).
2. Reduce your transaction to the bare minimum (ie: if you need to calculate the string that contains the query then do it before you start the transaction). Limiting in time an open transaction can help with high volume apps.
3. Have a good maintenance plan. Sometimes fragmented indexes can have an impact on concurrences issues (avoid the use of "SELECT * From Table")

This is just the beginning, every application and database has his own set of rules for managing a better concurrences.

这篇关于如何解决数据库并发问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！