Hi Jabber, I have a simple problem for database table locking. Why it not always working properly?

cyin003 (Jerry)
本文发表在 rolia.net/zh 相约加拿大网上社区枫下论坛
Hi Jabber,

I am working on a project involving servlet or JSP and MS SQL Server. It is a IIS Server + Resin structure.

Now, I need to insert a record into a table and id number is auto-generated when inserting data. I need the id number after insertion. I lock the table during the inserting and quering the id by defining it as a transaction to prevent other database user operating the table.

But the problem is that sometimes a null is retured from the id query. Here is the code:

// insert status info
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
stmt.execute();
stmt = con.prepareStatement("BEGIN TRANSACTION");
stmt.execute();
stmt = con.prepareStatement("insert into import_status WITH (TABLOCKX) (site_user_id, type, progress) values (?,?,?) ");
stmt.setString(1, dbc.getUserID());
stmt.setString(2, group_name);
stmt.setString(3, "Now is processing uploaded file");
stmt.executeUpdate();
// get the import_status_id
// Thread.sleep(3000); // no use
stmt = con.prepareStatement("select SCOPE_IDENTITY()");
//stmt = con.prepareStatement("SELECT @@IDENTITY AS \'Identity\'"); // no use. same thing
ResultSet rs = stmt.executeQuery();
if ( rs.next() )
import_status_id = rs.getInt(1);
stmt = con.prepareStatement("COMMIT TRANSACTION");
stmt.execute();
System.err.println("import_status_id="+import_status_id);
if ( import_status_id == 0 )
throw new Exception("SQLException: import_status_id == 0");

Can you give any ideas about this. Thanks.
更多精彩文章及讨论,请光临枫下论坛. 网址: rolia.net/zh
(#32200@0)
2001-3-11 -05:00

回到话题: Hi Jabber, I have a simple problem for database table locking. Why it not always working properly?

回到论坛: HOME枫下论坛枫下论坛主坛工作学习IT技术讨论

URL:   
http://www.rolia.net/zh/post.php?f=0&p=32200