| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-03-22 : 06:56:22
|
| Hi everybody, My problem is as follows :I have an Unit of Measurement entry module in Visual Basic 6.0 - SQL Server 7.0 whose screen looks something like this :________________________________________________________________Unit of measurement : kgs----------------------------------------------------------------S/N.|UOM converted.|Part No.|Description............|.Conv. fact----------------------------------------------------------------..1.|pcs...........|214-1K00|Resistor 214 1K........|.......1000..2.|pcs...........|214-100K|Resistor 214 100K......|........700---------------------------------------------------------------- <Save> <Cancel>----------------------------------------------------------------<Add> <Modify> <Delete> <Find> <Prev> <Next> <First> <Last> <Exit>_______________________________________________________________When the <Modify> command button is clicked, I lock the rows in the detail block by opening an ADO recordset using the command :ADORecordSet.Open "BEGIN TRAN SELECT * FROM Conv_Master WITH (HOLDLOCK, ROWLOCK) WHERE Conv_Master.uom = 'kgs '", ADOConnection, Options:=adCmdTextAfter the <Save> command button is clicked, I execute the SQL commands on the connection for saving the rows modified and added e.g.ADOConnection.Execute "INSERT INTO Conv_Master ( uom, uom_conv, part_no, conv_fact ) VALUES( 'kgs ', 'pcs ', '9010-902', 2 ) COMMIT TRAN"But this command doesn't execute (error message "Timeout expired"), apparently because the rows are locked. But this shouldn't happen because the BEGIN TRAN of the previous command is on the same connection. Only if I close the row locking recordset before executing this command, it works.On the other hand I have an Item-Party master entry module whose screen looks something like this :______________________________________________________________________________________Part No. : 214-1K00--------------------------------------------------------------------------------------S/N..Party code.....Party name.......................Status.............Party part no.--------------------------------------------------------------------------------------..1..SPA-0001.......A.K. Nimakwala...................Supplier...........RM-0001-------------------------------------------------------------------------------------- <Save> <Cancel>--------------------------------------------------------------------------------------<Add> <Modify> <Delete> <Find> <Prev> <Next> <First> <Last> <Exit>______________________________________________________________________________________In this, on clicking the <Modify> command button, I lock the rows in the detail block by opening an ADO recordset using the command :ADORecordSet.Open "BEGIN TRAN SELECT * FROM ItemParty_Master WITH (HOLDLOCK, ROWLOCK) WHERE ItemParty_Master.part_no = '100-100K'", ADOConnection, Options:=adCmdTextAfter the <Save> command button is clicked, I execute the SQL commands on the connection for saving the rows modified and added e.g.ADOConnection.Execute "INSERT INTO ItemParty_Master ( part_no, party_code, party_part_no ) VALUES( '100-100K', 'MFA-0001', 'RM-0002 ' ) COMMIT TRAN"In this case this command works absolutely fine, as if all the commands were with the BEGIN TRAN of the first command and COMMIT TRAN of the second command. If COMMIT TRAN is not executed, the row is not saved.In both the entry modules I used the same locking and saving commands, but SQL Server seems to be reacting differently in them. What could be the problem ?Edited by - Utpal on 03/22/2002 07:04:29Edited by - Utpal on 03/22/2002 07:06:06Edited by - Utpal on 03/22/2002 07:51:14 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-22 : 09:06:14
|
| I would check two things:(1) Make absolutely sure you're using the same connection. Inspect the code to check and verify by running sp_who. Aside from VB6 you didn't specify your setup in much detail - are you using MTS or COM+ for connection pooling, for example?(2) See what locks are actually being held by running sp_lock. Make sure they're all from the same SPID.setBasedIsTheTruepath<O> |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-23 : 00:46:01
|
| I am using the following commands to open my connection :With Conn .Provider = Provider .Properties("User ID").Value = "sa" .Properties("Password").Value = "meridian" .Properties("Data Source").Value = "NANDI" .Properties("Initial Catalog").Value = "InvMEDI2001" .OpenEnd WithThe connection variable Conn is global and I am opening it only once at the beginning. I am using the same connection variable in both the entry modules. So the connection is the same. I am not using anything like MTS or COM+.The sp_lock status when running Unit of measurement master is as follows :spid...dbid...ObjId.......IndId..Type.Resource.........Mode.....Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 1......1......0...........0......DB....................S........GRANT6......1......0...........0......DB....................S........GRANT8......7......0...........0......DB....................S........GRANT9......1......0...........0......DB....................S........GRANT9......2......0...........0......DB....................S........GRANT9......7......0...........0......DB....................S........GRANT9......1......117575457...0......TAB...................IS.......GRANTThe sp_lock status when running Item-Party master is as follows :spid...dbid...ObjId.......IndId..Type.Resource.........Mode.....Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 1......1......0...........0......DB....................S........GRANT6......1......0...........0......DB....................S........GRANT7......1......0...........0......DB....................S........GRANT7......2......0...........0......DB....................S........GRANT7......7......0...........0......DB....................S........GRANT7......1......117575457...0......TAB...................IS.......GRANT8......7......0...........0......DB....................S........GRANT9......7......0...........0......DB....................S........GRANTIs there anything wrong ?There is one difference between the Conv_Master table used in the Unit of measurement master and the ItemParty_Master. The Conv_Master has database triggers that update other tables, whereas the trigger in ItemParty_Master does not update any table, it is used just for validating data input. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-23 : 03:41:17
|
| I removed the trigger from Conv_Master table and then tried out. It didn't make any difference. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-23 : 06:36:01
|
| I studied the save procedures of both the entry modules in step-by-step debug mode. At the start of the procedures, if the INSERT commands are executed, both of them behave in the same manner, i.e. the error message "Timeout expired" comes up. In the Item-Party master save procedure, after a field in the locking recordset (opened with "BEGIN TRAN SELECT ...") is accessed, using the command :mascan = Ascan( aparty_code, ADORecordset.Fields("party_code").Value ) ( Ascan is an UDF ), executing the INSERT command does not generate an error message and the row is saved to the database. This behaviour is preferable, because the rows in the detail block are locked until the changes have been committed. But I am not able to achieve this in the Unit of measurement master entry module. There on accessing the fields in the locking recordset does not make any difference.Please help. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-23 : 12:07:58
|
| To be blocked the system must be using another connection somewhere.Use the profiler to trace what is happenning - it can also tell you what spids are being used for the commands and which locks are taken.With the system you have the records will be locked until the user saves the changes - which means that if someone is slow (goes to lunch in between) then no one will be able to update these records.Also a (HOLDLOCK, ROWLOCK) does not stop another user getting a lock - so two users can get the lock at the same time which would prevent either from updating - maybe this is what is happenning.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-24 : 00:42:46
|
| I am still developing the application from a stand-alone computer. It is not installed on a server with multiple users using it. So the possibility of users simultaneously trying to update does not arise. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-24 : 19:00:20
|
| Still try using the profiler to see if you are creating two connections.To be blocked two connections must be involved.The other option is that the update is just taking a very long time.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-25 : 04:09:37
|
| Thanks nr ! I studied the events occuring in both the entry modules through the profiler. In the Item-Party master, the commands "BEGIN TRAN SELECT ..." and "INSERT ... COMMIT TRAN" are executing under the same Connection & SP IDs. But in the Unit of Measurement master, the commands "BEGIN TRAN SELECT ..." and "INSERT ... COMMIT TRAN" are executing under different Connection & SP IDs. No wonder the second operation is getting blocked. Why are they behaving differently under the same circumstances ? What is the solution to this problem ? Please help ! |
|
|
|
|
Transaction problem