CREATE FUNCTION f_NextT1() RETURNS INT AS BEGIN RETURN(SELECT MAX(T1)+1 FROM tb WITH(XLOCK,PAGLOCK)) END GO CREATE TABLE tb( T1 int PRIMARY KEY DEFAULT dbo.f_NextT1(), [name] varchar(10))
sqlcmd.CommandText = "select max(finterid) from t_dailout"; FInterID = Convert.ToInt32(sqlcmd.ExecuteScalar()) + 1; sqlcmd.CommandText = "select case when '" + odr["类型"] + "'='去电' then 1 else 2 end "; Direction = Convert.ToInt32(sqlcmd.ExecuteScalar()); sqlcmd.CommandText = "insert into t_dailout(finterid,fcustid,fdate,flenth,fout,fnumber,fbillerid,fnote) values('" + FInterID + "','0','" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", odr["开始时间"]) + "','" + odr["通话时间"] + "','" + Direction + "','" + odr["号码"] + "','"+Common.UserID+"','序列号:" + odr["序列号"] + "')"; FileStream stream = new FileStream(odr["录音文件"].ToString(), FileMode.Open); byte[] blob = new byte[stream.Length]; stream.Read(blob, 0, (int)stream.Length); stream.Close(); SqlCommand sqlinsertcmd = new SqlCommand("insert into kddail.dbo.t_exefile(finterid,ffile) values('" + FInterID + "',@ffile)", sqlcon); sqlinsertcmd.CommandTimeout = 0; sqlinsertcmd.Parameters.Add("@ffile", blob); sqlinsertcmd.ExecuteNonQuery(); 我之前的代码,我要从查得max(finterid)前加锁,在insert into两张表后解锁,请问要怎么加锁请问,我没接触过事务锁
建立辅助表可以防止多个进程获得相同的值 create table T_Identity ( val int identity(1,1)) create PROC usp_getIdentity @val int output as begin tran save tran t1 insert into T_Identity default values set @val=scope_identity() rollback tran t1 commit tran go---多执行几次看看 declare @v int exec usp_getIdentity @val=@v output select @v
RETURNS INT
AS
BEGIN
RETURN(SELECT MAX(T1)+1 FROM tb WITH(XLOCK,PAGLOCK))
END
GO
CREATE TABLE tb(
T1 int PRIMARY KEY DEFAULT dbo.f_NextT1(),
[name] varchar(10))
sqlcmd.CommandText = "select max(finterid) from t_dailout";
FInterID = Convert.ToInt32(sqlcmd.ExecuteScalar()) + 1;
sqlcmd.CommandText = "select case when '" + odr["类型"] + "'='去电' then 1 else 2 end ";
Direction = Convert.ToInt32(sqlcmd.ExecuteScalar());
sqlcmd.CommandText = "insert into t_dailout(finterid,fcustid,fdate,flenth,fout,fnumber,fbillerid,fnote) values('" + FInterID + "','0','" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", odr["开始时间"]) + "','" + odr["通话时间"] + "','" + Direction + "','" + odr["号码"] + "','"+Common.UserID+"','序列号:" + odr["序列号"] + "')";
FileStream stream = new FileStream(odr["录音文件"].ToString(), FileMode.Open);
byte[] blob = new byte[stream.Length];
stream.Read(blob, 0, (int)stream.Length);
stream.Close();
SqlCommand sqlinsertcmd = new SqlCommand("insert into kddail.dbo.t_exefile(finterid,ffile) values('" + FInterID + "',@ffile)", sqlcon);
sqlinsertcmd.CommandTimeout = 0;
sqlinsertcmd.Parameters.Add("@ffile", blob);
sqlinsertcmd.ExecuteNonQuery();
我之前的代码,我要从查得max(finterid)前加锁,在insert into两张表后解锁,请问要怎么加锁请问,我没接触过事务锁
create table T_Identity
( val int identity(1,1))
create PROC usp_getIdentity
@val int output
as
begin tran
save tran t1
insert into T_Identity default values
set @val=scope_identity()
rollback tran t1
commit tran
go---多执行几次看看
declare @v int
exec usp_getIdentity @val=@v output
select @v