JSP+SQL Server 2000的数据库写流水号的问题

[Eraserpro]

要完成的一项工作是生成序列号并写入表A。
表A结构：ID(int,自动增长,主键) SerialNo:(int) ......
现在要做的是访问表A，插入一行数据，SerialNo必须等于ID号(ID是自动增长的)，最后返回SerialNo号供其他操作使用
数据库SQL Server 2000 服务器：Tomcat 4.1.27
之前的做法是用一个select MAX(ID) from A来获取前一条记录，把前一记录的ID加一做为接下来要写入的那行数据的SerialNo然后写入数据。
访问的人少时没出问题，但访问的人多起来后就不清楚了。
(奇怪的要求，SerialNo和ID相同会造成数据冗余，但如果改动表我要改超多页面，没心思，都是之前那位程序员惹的祸)

想问两个问题：
1、能不能用一条SQL语句完成这个写操作？（serialNo与ID相同）
2、之前的做法是否会出问题？换句话说，jsp的执行会不会出现这种情况：a.jsp和b.jsp内容完全一样，但位于不同目录，当两个人分别访问a.jsp,b.jsp的时候(同时访问)，会不会出现a.jsp中的select MAX(ID) from A得到了数据比如是25，此时a.jsp应该写入ID:（自动增长到26,）SerialNo:26，但如果insert操作还未执行之前，b.jsp的select和insert操作已经完成了(写入ID:26 SerialNo:26)？
这样的话a.jsp写入的记录其实是ID:27 SerialNo:26,造成了SerialNo的重复
也就是说a.jsp之前select得到的是“脏”数据？(用存储过程好像可以通过锁定数据库来避免这种情况，但是我不想用过程)我不清楚jsp的执行是否会出现这种情况！

我现在想把它改成用同一个JavaBean，范围是application,然后把select,insert,再select返回insert操作写入的SerialNo号这些操作写在一个方法里，把这个方法设为synchronized，不过总是没底，不知道能不能解决这个问题

谁有比较好的解决方法？类似这种又要select又要insert还要考虑并发的情况？
200分，up有分

[叶子哟]

你可以做一个存储过程,在插入时返回插入记录的ID就可以了

[dooby]

你之前的做法的确会出错误，错误原因你写的很正确

我以前的做法是
专门建立一个序列值的的表

一个是序列的名称，一个是数值

每次取序列的时候
先update xlb set xlz=xlz+1 where xlm ='xxx'
再select xlz from xlb where xlm='xxx'

但是保证这两条sql是在同一事务声明里面的

[liad]

请尝试在sql server的查询分析器上执行下列语句(一次性执行):

create table a
(
a0 as a1, -- 这时你在设计表窗口里看到a0的Formula属性为: ([a1])
a1 int identity(1,1),
a2 varchar(50)
);
go

insert into a(a2) values('Hello');
go

select @@IDENTITY as inserted_id

select * from a;
go

希望能对你有帮助

[Eraserpro]

多谢各位，事实上SerialNo这个字段根本就是多余的，但我也没办法，前一个程序员做的东西留下来就这样，改表结构的话就会有一大堆程序要改！
根据各位的思路，我已经想到一个办法用一条SQL语句实现（只适用于SQL Server 2K）,没实验过不好说。
但现在又产生一个问题，我insert一行后要用一个select把刚insert进去的一流水号读出并返回，如何保证这个select读出的流水号是刚写进去的那个流水号呢？
好像可以用事务，但我没有写过insert后再select的事务，哪位能给我一个例子，能保证多用户并发情况select读出的是同一jsp文件写入的流水号？
谢谢！！！

[mtou]

我的意见是在
表A结构：ID(int,自动增长,主键) SerialNo:(int) ......
中不包含SerialNo:(int) ,然后建一个临时表把表A的内容读进去,然后对SerialNo:(int) 排序

select identity(int,1,1) as SerialNo,表A字段(除了ID以外的字段) into #a from 表A
select * from #a
drop talbe #a

[remanwang]

1.建立一个transaction
2.insert into A(SerialNo) values(0)
3.select @@IDENTITY as theid
4.update A set SerialNo=theid where id=theid
5.commit

[jokerjava]

好像双表很流行

[Geranium]

关注~

[killerdanny]

你要建立一个流水表，里面存的当前最大的流水号。

每次INSERT，先SELECT流水，然后INSERT A ，最后UPDATE 流水

[spiritsl]

既然ID是自动增长的，就是说有一个序列在产生它，而SerialNo又要等于id，那这两项都写入ID号，不可以吗？

[minimu]

SerialNo首先留空，为insert写一个触发器，找到SerialNo为null的值，赋ID就可以了，其实一般只有一条的，确实不放心的话用游标循环一下

[1981520baby]

概括起来说就是在插入记录的那一刹那取得最大记录号。。

[Eraserpro]

不要嫌我罗嗦,请问触发器执行的时候是不是会将表锁定的?
如果不是的话就有可能读到其他并发用户刚刚生成的记录
还是会出错
哪位清楚
(抱歉,不是我喜欢钻牛角尖,只是有点不放心想问清楚)

[liad]

请问你对这个解决方案还有什么疑问？不可行吗？
create table a
(
a0 as a1, -- 这时你在设计表窗口里看到a0的Formula属性为: ([a1])
a1 int identity(1,1),
a2 varchar(50)
);

[Eraserpro]

换句话说就是怎样在事务中加入select查询语句?该加在哪里?conn.commit()方法应该在什么时候执行?需要注意什么?
有例子吗?

[Eraserpro]

结帖了
最后我用了一个所有插入流水号的JSP文件都使用的JavaBean,scope为application,插入流水号的工作交给过程dt_makeSerialNo来做,insert into a(SerialNo) values(@@IDENTITY+1)然后再select @@IDENTITY(事实上我用的是另一个函数IDENTI???(忘了),比@@IDENTITY更准确一些)
把这个过程加入一个事务,这个事务放在那个JavaBean的makeNo方法中,同时把该方法设为synchronized,保证同一时间只能有一个文件能够使用该方法,这样子应该没什么大问题了.

多谢各位朋友的帮忙!!!

[Eraserpro]

TO: liad()
你的方案插入流水号时是可行的,我试过了,很方便
不过插入流水号的问题解决之后我更关心的是INSERT之后如何SELECT刚写入的那条记录的ID

今晚十点结帖,各位如果还有什么方案请说!

[liad]

http://www.kamath.com/tutorials/tut007_identity.asp
.....................
Enter @@IDENTITY
When a record is inserted into a table with a identity column, the function @@IDENTITY (or the global variable @@IDENTITY as in SQL Server 6.5) returns the last identity value that was inserted in the database. I emphasize the word "last identity value" here because, this maybe different from the identity value of that particular table where the record was inserted.

Why, You may ask. When a record is inserted, any underlying triggers may modify other tables. If a trigger adds a record into another table, which happens to have an identity column, @@IDENTITY will now return this value instead. This is a point to note when retrieving the identity values. Also, if the trigger inserts into a table without any identity column, the @@IDENTITY will be null. For now, let us assume that the tables do not have any triggers and there is just one INSERT statement.

Another point to note is, the @@IDENTITY returns the last identity value on the same open connection to the database. By connection, I mean a physical connection. So, if you connect to the database, do an INSERT, disconnect and connect back again, the @@IDENTITY value will be NULL. On the other hand, if you INSERT into a table, retrieve @@IDENTITY, keep the connection open, get the @@IDENTITY again you will still get the same value as the first retrieval. In summary, you can treat @@IDENTITY as a connection specific global variable.