数据访问层代码:
public void NWHJYXsetData(int alid, int studentalid,string bzcode,int usekz)
{
string selecthaveStudent = "select ID from tp_bz_wdbz where WDBZ_ALID=" + studentalid + " AND WDBZ_BZCode='" + bzcode + "'";
object selectstudent = TPMySqlHelper.ExecuteScalar(selecthaveStudent);
if (selectstudent == null)
{
MySqlParameter[] sp = new MySqlParameter[]
{
new MySqlParameter("alid",alid),
new MySqlParameter("stuAlid",studentalid),
new MySqlParameter("bzcode",bzcode),
new MySqlParameter("usekz",usekz)
};
TPMySqlHelper.RunProcedureNull("sp_ALFX", sp);
}
}数据库公用类方法 /// <summary>
/// 执行存储过程,返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, Object[] parameters, out int rowsAffected)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
int result;
connection.Open();
MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
存储过程 :
BEGIN
DECLARE BackBZID INT;
DECLARE OldBZid INT;
/*分析案例*/select ID INTO OldBZid from tp_bz_wdbz where WDBZ_ALID=alid AND WDBZ_BZCode=WDBZ_BZCodes;insert into tp_bz_wdbz (WDBZ_ALID,WDBZ_SFWC,WDBZ_BT,WDBZ_ZWT,WDBZ_BZCode)
select BackAlID ,WDBZ_SFWC,WDBZ_BT,WDBZ_ZWT,WDBZ_BZCode from tp_bz_wdbz
where WDBZ_ALID=alid AND WDBZ_BZCode=WDBZ_BZCodes;select @@IDENTITY INTO BackBZID;
IF NEXT=1 THEN
insert into tp_bz_kz_wdbz (KZWDBZ_BZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode)
select BackBZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode from tp_bz_kz_wdbz
where KZWDBZ_BZID=OldBZid AND KZWDBZ_BZCode=WDBZ_BZCodes;
END IF;
/*分析案例*/
END报错信息:“/”应用程序中的服务器错误。
--------------------------------------------------------------------------------Deadlock found when trying to get lock; try restarting transaction
......多人同时操作会报错,大家帮忙看下是什么原因,在线急等,万分感激。。
public void NWHJYXsetData(int alid, int studentalid,string bzcode,int usekz)
{
string selecthaveStudent = "select ID from tp_bz_wdbz where WDBZ_ALID=" + studentalid + " AND WDBZ_BZCode='" + bzcode + "'";
object selectstudent = TPMySqlHelper.ExecuteScalar(selecthaveStudent);
if (selectstudent == null)
{
MySqlParameter[] sp = new MySqlParameter[]
{
new MySqlParameter("alid",alid),
new MySqlParameter("stuAlid",studentalid),
new MySqlParameter("bzcode",bzcode),
new MySqlParameter("usekz",usekz)
};
TPMySqlHelper.RunProcedureNull("sp_ALFX", sp);
}
}数据库公用类方法 /// <summary>
/// 执行存储过程,返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, Object[] parameters, out int rowsAffected)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
int result;
connection.Open();
MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
存储过程 :
BEGIN
DECLARE BackBZID INT;
DECLARE OldBZid INT;
/*分析案例*/select ID INTO OldBZid from tp_bz_wdbz where WDBZ_ALID=alid AND WDBZ_BZCode=WDBZ_BZCodes;insert into tp_bz_wdbz (WDBZ_ALID,WDBZ_SFWC,WDBZ_BT,WDBZ_ZWT,WDBZ_BZCode)
select BackAlID ,WDBZ_SFWC,WDBZ_BT,WDBZ_ZWT,WDBZ_BZCode from tp_bz_wdbz
where WDBZ_ALID=alid AND WDBZ_BZCode=WDBZ_BZCodes;select @@IDENTITY INTO BackBZID;
IF NEXT=1 THEN
insert into tp_bz_kz_wdbz (KZWDBZ_BZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode)
select BackBZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode from tp_bz_kz_wdbz
where KZWDBZ_BZID=OldBZid AND KZWDBZ_BZCode=WDBZ_BZCodes;
END IF;
/*分析案例*/
END报错信息:“/”应用程序中的服务器错误。
--------------------------------------------------------------------------------Deadlock found when trying to get lock; try restarting transaction
......多人同时操作会报错,大家帮忙看下是什么原因,在线急等,万分感激。。
解决方案 »
- 使用mysql作为数据库服务器,如何在客户端打包安装mysql连接组件?
- 查一个uid在每一天出现的次数,即请教mysql语句两次分组怎么写
- Mysql 插入临时表is full 怎么动态设置临时表大小
- 求助,mysql存储过程
- mysql 怪异的问题,急。。
- mysql导入setup.sql没成功?
- 高手来看看,下面MSSQL语句,怎么用MYSQL实现!
- mysql数据类型的使用
- mysql root权限不小心被干掉了,已经访问不了权限数据库'mysql'了,请问如何恢复?
- Linux PID 与 MySql的processlist id的对应关系是什么??
- mysql存储过程中游标问题
- mysql 权限问题
select BackAlID ,WDBZ_SFWC,WDBZ_BT,WDBZ_ZWT,WDBZ_BZCode from tp_bz_wdbz
where WDBZ_ALID=alid AND WDBZ_BZCode=WDBZ_BZCodes;
在WDBZ_ALID和WDBZ_BZCode上建立索引
alter table tp_bz_wdbz add index(WDBZ_ALID,WDBZ_BZCode)
mysql tables in use 2, locked 2
LOCK WAIT 20 lock struct(s), heap size 2496
MySQL thread id 12071, query id 913270 ALLEN-PC 192.168.1.101 root Sending data
insert into tp_bz_kz_wdbz (KZWDBZ_BZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode)
select NAME_CONST('BackBZID',14673),KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode from tp_bz_kz_wdbz
where KZWDBZ_BZID= NAME_CONST('OldBZid',14096) AND KZWDBZ_BZCode= NAME_CONST('WDBZ_BZCodes',_utf8 0x414C4658)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `importdatatopway2/tp_bz_kz_wdbz` trx id 0 3565510 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 3565509, ACTIVE 1 sec, OS thread id 2096 inserting, thread declared inside InnoDB 500
mysql tables in use 2, locked 2
22 lock struct(s), heap size 2496
MySQL thread id 12077, query id 913267 07f420bebe61427 192.168.1.106 root Sending data
insert into tp_bz_kz_wdbz (KZWDBZ_BZID,KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode)
select NAME_CONST('BackBZID',14672),KZWDBZ_WT,KZWDBZ_HD,KZWDBZ_BZCode from tp_bz_kz_wdbz
where KZWDBZ_BZID= NAME_CONST('OldBZid',14096) AND KZWDBZ_BZCode= NAME_CONST('WDBZ_BZCodes',_utf8 0x414C4658)
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `importdatatopway2/tp_bz_kz_wdbz` trx id 0 3565509 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1327162 n bits 112 index `PRIMARY` of table `importdatatopway2/tp_bz_kz_wdbz` trx id 0 3565509 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 0 page no 1327162 n bits 112 index `PRIMARY` of table `importdatatopway2/tp_bz_kz_wdbz` trx id 0 3565509 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;