Record locking ADO -net
-
Hello, I have windows form application that is updating an sql2k db. Because we want serial keys we have decide to not use SQL2k to generate the keys. To facilitate this we have a table that holds the last key (lastkey) for each table. Right now for each insert into a table this key table must be hit and updated. So in code we have a Select lastkey form tbKey into an ADO recordset Update tbKey set lastkey = rs(0)+1 Now that has worked for 3 years. Yesterday it broke. Two applications selected lastkey at the same time and thus inserting the same PK into a table. That sucked what sucked more is that it was a published database so it took out all of the subscribers. I’ve come up with this bit of sql to update and get the the key at the same time, within one record lock. declare @Last_System_id int update tbkey set tbkey.lastkey =(lastkey+1), @Last_System_id = lastkey+1 where tbname='sometablename' select @Last_System_id as myKey I can’t figure out how to execute it with ADO and VB6. Actually it executes just fine. I can’t figure out how to get that select to return into a recordset. I’ve tried this.
Dim sUpdate as string Dim NewKey as int sUpdate = “declare @Last_System_id int update tbkey set tbkey.lastkey =(lastkey+1), Last_System_id = lastkey+1 where tbname='sometablename' select @Last_System_id as myKey”Connection.open Set my_Recordset = Connection.Execute(sUpdate) NewKey = my_Recordset(0)My_recordset is empty I also tried:My_Recordset(1) My_Recordset(“myKey”)All give me this error “Run-time error ‘3265’ Item cannot be found in the collection corresponding to the requested name or ordinal.” Any insight will be much appreciated. Thanks Ron Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net -
Hello, I have windows form application that is updating an sql2k db. Because we want serial keys we have decide to not use SQL2k to generate the keys. To facilitate this we have a table that holds the last key (lastkey) for each table. Right now for each insert into a table this key table must be hit and updated. So in code we have a Select lastkey form tbKey into an ADO recordset Update tbKey set lastkey = rs(0)+1 Now that has worked for 3 years. Yesterday it broke. Two applications selected lastkey at the same time and thus inserting the same PK into a table. That sucked what sucked more is that it was a published database so it took out all of the subscribers. I’ve come up with this bit of sql to update and get the the key at the same time, within one record lock. declare @Last_System_id int update tbkey set tbkey.lastkey =(lastkey+1), @Last_System_id = lastkey+1 where tbname='sometablename' select @Last_System_id as myKey I can’t figure out how to execute it with ADO and VB6. Actually it executes just fine. I can’t figure out how to get that select to return into a recordset. I’ve tried this.
Dim sUpdate as string Dim NewKey as int sUpdate = “declare @Last_System_id int update tbkey set tbkey.lastkey =(lastkey+1), Last_System_id = lastkey+1 where tbname='sometablename' select @Last_System_id as myKey”Connection.open Set my_Recordset = Connection.Execute(sUpdate) NewKey = my_Recordset(0)My_recordset is empty I also tried:My_Recordset(1) My_Recordset(“myKey”)All give me this error “Run-time error ‘3265’ Item cannot be found in the collection corresponding to the requested name or ordinal.” Any insight will be much appreciated. Thanks Ron Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.netI have done something similar. My solution is to generate the Primary Key using a SP. CREATE TABLE dbo.Sequence ( TableName varchar(30) NOT NULL, NextVal int NOT NULL ) LOCK DATAROWS go IF OBJECT_ID('dbo.Sequence') IS NOT NULL PRINT '<<< CREATED TABLE dbo.Sequence >>>' ELSE PRINT '<<< FAILED CREATING TABLE dbo.Sequence >>>' go CREATE PROCEDURE GetSequence ( @tbl varchar(30)) AS DECLARE @nextval int BEGIN BEGIN TRAN UPDATE Sequence SET NextVal = NextVal + 1 WHERE TableName = @tbl IF @@rowcount = 1 SELECT @nextval = NextVal FROM Sequence WHERE TableName = @tbl COMMIT TRAN SELECT @nextval END Hope this helps.
-
I have done something similar. My solution is to generate the Primary Key using a SP. CREATE TABLE dbo.Sequence ( TableName varchar(30) NOT NULL, NextVal int NOT NULL ) LOCK DATAROWS go IF OBJECT_ID('dbo.Sequence') IS NOT NULL PRINT '<<< CREATED TABLE dbo.Sequence >>>' ELSE PRINT '<<< FAILED CREATING TABLE dbo.Sequence >>>' go CREATE PROCEDURE GetSequence ( @tbl varchar(30)) AS DECLARE @nextval int BEGIN BEGIN TRAN UPDATE Sequence SET NextVal = NextVal + 1 WHERE TableName = @tbl IF @@rowcount = 1 SELECT @nextval = NextVal FROM Sequence WHERE TableName = @tbl COMMIT TRAN SELECT @nextval END Hope this helps.
Thank you for the reply. At this shop we don’t like using SP’s. The best reason I can come up with is that the boss simply doesn’t trust MS enough to let SQL2k handle such things. But that is a cool bit of code thanks. We have come up with this solution on the software side. We will no longer care if there is a multiple or Phantom read from the key table, the function will take what ever key it gets; however, on the insert, if a phantom read has occurred, we will catch the Duplicate PK error generated by sql and go back and grab another new Key. I know that I specified that we needed a serial key but this solution will be nearly 100% serial and for the times that the sequins is broken there will only be, statistically, one or two missed. And that won’t hurt us. The reason we need serial is to make DB maintenance easer. Simply put one error try again. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Thank you for the reply. At this shop we don’t like using SP’s. The best reason I can come up with is that the boss simply doesn’t trust MS enough to let SQL2k handle such things. But that is a cool bit of code thanks. We have come up with this solution on the software side. We will no longer care if there is a multiple or Phantom read from the key table, the function will take what ever key it gets; however, on the insert, if a phantom read has occurred, we will catch the Duplicate PK error generated by sql and go back and grab another new Key. I know that I specified that we needed a serial key but this solution will be nearly 100% serial and for the times that the sequins is broken there will only be, statistically, one or two missed. And that won’t hurt us. The reason we need serial is to make DB maintenance easer. Simply put one error try again. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
Sounds like you also don't trust SQL2k to generate the PK as an identity either, if you did the simple solution would be to make the PK field an Integer identity type with an increment of 1...eliminating the need for the PK table and the round trip to get the pk. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke
-
Sounds like you also don't trust SQL2k to generate the PK as an identity either, if you did the simple solution would be to make the PK field an Integer identity type with an increment of 1...eliminating the need for the PK table and the round trip to get the pk. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke
Agreed. I dont trust SQL2k to generate the PK as an identity. I have seen in some situations when the Server crashes enormous identity gaps are created. The only solution in this case is to BCP out the data, drop and recreate the table abd then BCP in. It has happened twice with me. So I dont use identity columns anymore. Live Life King Size Alomgir Miah