Re: [DUG] MSSQL Question
Kyley my experience is with postgreqsl, it has sequences (I assume from its oracleness), which get tied to a column via the serial type. I'm puzzled as to where you would use a Sequence/Generator in a non-pk situation, unless you are talking about your 'documentno' column, which as shown can be done via a stored proc I wonder is mssql eventually will allow functions to update tables (or code a primative sequence generation) which would allow this Neven The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice) John ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Where I am mainly using Sequences, not tied to a specific field is where many entities in our applicationall use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs. CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int AS SET NOCOUNT ON DECLARE @AVALUE Int BEGIN TRAN DECLARE @Res TABLE ( ID int ) BEGIN UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK) SET KEYVALUE = @[EMAIL PROTECTED] OUTPUT deleted.KEYVALUE INTO @RES(ID) WHERE KEYNAME = @TableName if @@ROWCOUNT = 0 begin SET @AVALUE = 1 INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1); end else begin SELECT @AVALUE=ID from @Res end END COMMIT TRAN RETURN @AVALUE On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan [EMAIL PROTECTED] wrote: Kyley my experience is with postgreqsl, it has sequences (I assume from its oracleness), which get tied to a column via the serial type. I'm puzzled as to where you would use a Sequence/Generator in a non-pk situation, unless you are talking about your 'documentno' column, which as shown can be done via a stored proc I wonder is mssql eventually will allow functions to update tables (or code a primative sequence generation) which would allow this Neven The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice) John ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin:
Re: [DUG] MSSQL Question
Kyley Looks pretty robust (explicit locks), I've never used the output pseudotable structure (interesting, learn something new), If you set the TRANSACTION ISOLATION to SERIALIZED could you drop the explicit locks? and that would also mean that your insert was protected? There may be a SLIM chance, that if you have a very low TRAN ISO and you called GENGEN almost simultaneously WITH the same unititialized @TableName that the @@Rowcount for the second call would be incorrect (as there would be a race to insert) What is your TRANSACTION ISOLATION? Neven Where I am mainly using Sequences, not tied to a specific field is where many entities in our application all use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs. CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int AS SET NOCOUNT ON DECLARE @AVALUE Int BEGIN TRAN DECLARE @Res TABLE ( ID int ) BEGIN UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK) SET KEYVALUE = @[EMAIL PROTECTED] OUTPUT deleted.KEYVALUE INTO @RES(ID) WHERE KEYNAME = @TableName if @@ROWCOUNT = 0 begin SET @AVALUE = 1 INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1); end else begin SELECT @AVALUE=ID from @Res end END COMMIT TRAN RETURN @AVALUE On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Kyley my experience is with postgreqsl, it has sequences (I assume from its oracleness), which get tied to a column via the serial type. I'm puzzled as to where you would use a Sequence/Generator in a non-pk situation, unless you are talking about your 'documentno' column, which as shown can be done via a stored proc I wonder is mssql eventually will allow functions to update tables (or code a primative sequence generation) which would allow this Neven The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice)
Re: [DUG] MSSQL Question
Transaction Isolation keeps changing (my mind that is). Trying to work out what is best. I was trying snapshot, so that it mimics interbase. I may well just leave it on read/committed. This is one of those subject where I use Prayer as a means of making it work :) I also realized that the insert was the micro risk.. didn't know serialized would protect it. I'll try that and see. Thanks for the advise On Tue, Dec 2, 2008 at 8:08 AM, Neven MacEwan [EMAIL PROTECTED] wrote: Kyley Looks pretty robust (explicit locks), I've never used the output pseudotable structure (interesting, learn something new), If you set the TRANSACTION ISOLATION to SERIALIZED could you drop the explicit locks? and that would also mean that your insert was protected? There may be a SLIM chance, that if you have a very low TRAN ISO and you called GENGEN almost simultaneously WITH the same unititialized @TableName that the @@Rowcount for the second call would be incorrect (as there would be a race to insert) What is your TRANSACTION ISOLATION? Neven Where I am mainly using Sequences, not tied to a specific field is where many entities in our application all use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs. CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int AS SET NOCOUNT ON DECLARE @AVALUE Int BEGIN TRAN DECLARE @Res TABLE ( ID int ) BEGIN UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK) SET KEYVALUE = @[EMAIL PROTECTED] OUTPUT deleted.KEYVALUE INTO @RES(ID) WHERE KEYNAME = @TableName if @@ROWCOUNT = 0 begin SET @AVALUE = 1 INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1); end else begin SELECT @AVALUE=ID from @Res end END COMMIT TRAN RETURN @AVALUE On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Kyley my experience is with postgreqsl, it has sequences (I assume from its oracleness), which get tied to a column via the serial type. I'm puzzled as to where you would use a Sequence/Generator in a non-pk situation, unless you are talking about your 'documentno' column, which as shown can be done via a stored proc I wonder is mssql eventually will allow functions to update tables (or code a primative sequence generation) which would allow this Neven The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem.
Re: [DUG] MSSQL Question
I have a feeling one of my main issues here is isolation level. I have tried to configure a Generic connection setup for running in my middle layer. Good for most, but in some cases possibly going to cause issues. I may need to allow for different server tasks to decide the correct isolation level for their task.. ie readonly isolation, update isolation, or snapshot if running a report. All the work is done by the middlelayer.. most of the work is ReadOnly, and then when committing data its all done in object and quickly thrown into the database, so typically as I said before, and update transaction is 100ms or less.. the longest transactions are the reports, our longest report execute time so far is about 6-8 seconds and thats all read only transaction.. I thought Snapshot isolation is best for reporting with concurrent data.. Hmm in interbase it versions off data without locking other transactions.. does MSSQL just block? This is why I like StarTrek.. Computer.. make it go! On Tue, Dec 2, 2008 at 10:29 AM, Kyley Harris [EMAIL PROTECTED] wrote: Transaction Isolation keeps changing (my mind that is). Trying to work out what is best. I was trying snapshot, so that it mimics interbase. I may well just leave it on read/committed. This is one of those subject where I use Prayer as a means of making it work :) I also realized that the insert was the micro risk.. didn't know serialized would protect it. I'll try that and see. Thanks for the advise On Tue, Dec 2, 2008 at 8:08 AM, Neven MacEwan [EMAIL PROTECTED] wrote: Kyley Looks pretty robust (explicit locks), I've never used the output pseudotable structure (interesting, learn something new), If you set the TRANSACTION ISOLATION to SERIALIZED could you drop the explicit locks? and that would also mean that your insert was protected? There may be a SLIM chance, that if you have a very low TRAN ISO and you called GENGEN almost simultaneously WITH the same unititialized @TableName that the @@Rowcount for the second call would be incorrect (as there would be a race to insert) What is your TRANSACTION ISOLATION? Neven Where I am mainly using Sequences, not tied to a specific field is where many entities in our application all use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs. CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int AS SET NOCOUNT ON DECLARE @AVALUE Int BEGIN TRAN DECLARE @Res TABLE ( ID int ) BEGIN UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK) SET KEYVALUE = @[EMAIL PROTECTED] OUTPUT deleted.KEYVALUE INTO @RES(ID) WHERE KEYNAME = @TableName if @@ROWCOUNT = 0 begin SET @AVALUE = 1 INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1); end else begin SELECT @AVALUE=ID from @Res end END COMMIT TRAN RETURN @AVALUE On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Kyley my experience is with postgreqsl, it has sequences (I assume from its oracleness), which get tied to a column via the serial type. I'm puzzled as to where you would use a Sequence/Generator in a non-pk situation, unless you are talking about your 'documentno' column, which as shown can be done via a stored proc I wonder is mssql eventually will allow functions to update tables (or code a primative sequence generation) which would allow this Neven The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity
Re: [DUG] MSSQL Question
Kyley wrote: I thought Snapshot isolation is best for reporting with concurrent data.. Hmm in interbase it versions off data without locking other transactions.. does MSSQL just block? In versions prior to SQL Server 2005, yes. It used to just block writes to any unread row until it had been streamed to the requesting client. So, readers blocked writers and writers blocked readers. The worst of both worlds. With SQL 2005, they implemented a solution similar to Oracles rollback segments to support snapshot read isolation without blocking writes. In fact, SQL 2005 specifically addressed the prime concern that stopped sites migrating away from Oracle. Funny that :-) It's still not as 'nice' an implementation as Firebird/Interbase/Postgresql (i.e. proper MVCC). Oracle can exhaust it's rollback segments (the dreaded ORA-01555) and SQL Server can run out of TempDB space. This usually occurs in the face of long running reporting transactions against a database facing high insertion or mutation rates. In these cases, both Oracle and SQL Server abort the reporting transaction which is rather 'unhelpful' but given their implementation, they have no other option. Anyway, to avoid reference to prayer and to understand more of how this all works internally I recommend the late Jim Gray's book 'Transaction Processing: Concepts and Techniques'. It's pretty much the Bible for transaction processing IMO. It was invaluable when I was implementing client-side transaction processing in the Accredo database engine. TTFN, Paul. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Thanks. Its an issue when every database vendor does it wildly different.. sigh...I haven't been using MSSQL a long time. I like it.. but sometimes I dont :) On Tue, Dec 2, 2008 at 10:58 AM, Paul Heinz [EMAIL PROTECTED] wrote: Kyley wrote: I thought Snapshot isolation is best for reporting with concurrent data.. Hmm in interbase it versions off data without locking other transactions.. does MSSQL just block? In versions prior to SQL Server 2005, yes. It used to just block writes to any unread row until it had been streamed to the requesting client. So, readers blocked writers and writers blocked readers. The worst of both worlds. With SQL 2005, they implemented a solution similar to Oracles rollback segments to support snapshot read isolation without blocking writes. In fact, SQL 2005 specifically addressed the prime concern that stopped sites migrating away from Oracle. Funny that :-) It's still not as 'nice' an implementation as Firebird/Interbase/Postgresql (i.e. proper MVCC). Oracle can exhaust it's rollback segments (the dreaded ORA-01555) and SQL Server can run out of TempDB space. This usually occurs in the face of long running reporting transactions against a database facing high insertion or mutation rates. In these cases, both Oracle and SQL Server abort the reporting transaction which is rather 'unhelpful' but given their implementation, they have no other option. Anyway, to avoid reference to prayer and to understand more of how this all works internally I recommend the late Jim Gray's book 'Transaction Processing: Concepts and Techniques'. It's pretty much the Bible for transaction processing IMO. It was invaluable when I was implementing client-side transaction processing in the Accredo database engine. TTFN, Paul. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Thanks everyone. I've decided to leave the DB on Read/Commited, Reports on Repeatable Read, and as neven said Serializable in the single stored proc and remove the lock hints. Should work :) On Tue, Dec 2, 2008 at 11:03 AM, Kyley Harris [EMAIL PROTECTED] wrote: Thanks. Its an issue when every database vendor does it wildly different.. sigh...I haven't been using MSSQL a long time. I like it.. but sometimes I dont :) On Tue, Dec 2, 2008 at 10:58 AM, Paul Heinz [EMAIL PROTECTED] wrote: Kyley wrote: I thought Snapshot isolation is best for reporting with concurrent data.. Hmm in interbase it versions off data without locking other transactions.. does MSSQL just block? In versions prior to SQL Server 2005, yes. It used to just block writes to any unread row until it had been streamed to the requesting client. So, readers blocked writers and writers blocked readers. The worst of both worlds. With SQL 2005, they implemented a solution similar to Oracles rollback segments to support snapshot read isolation without blocking writes. In fact, SQL 2005 specifically addressed the prime concern that stopped sites migrating away from Oracle. Funny that :-) It's still not as 'nice' an implementation as Firebird/Interbase/Postgresql (i.e. proper MVCC). Oracle can exhaust it's rollback segments (the dreaded ORA-01555) and SQL Server can run out of TempDB space. This usually occurs in the face of long running reporting transactions against a database facing high insertion or mutation rates. In these cases, both Oracle and SQL Server abort the reporting transaction which is rather 'unhelpful' but given their implementation, they have no other option. Anyway, to avoid reference to prayer and to understand more of how this all works internally I recommend the late Jim Gray's book 'Transaction Processing: Concepts and Techniques'. It's pretty much the Bible for transaction processing IMO. It was invaluable when I was implementing client-side transaction processing in the Accredo database engine. TTFN, Paul. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Kyley does MSSQL just block? I would expect that how it behaves depends on the isolation level and what you are doing, I could be wrong here (and I'm sure Paul will correct me :-) But the more severe the isolation level, the more severe the locks and how long they persist for From MSDN SERIALIZABLE Specifies the following: * Statements cannot read data that has been modified but not yet committed by other transactions. * No other transactions can modify data that has been read by the current transaction until the current transaction completes. * Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes. Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction. so if you opened GENGEN with BEGIN TRANSACTION SELECT * FROM GEN_IDTABLE you would put an exclusive lock on the table until finished Neven This is why I like StarTrek.. Computer.. make it go! On Tue, Dec 2, 2008 at 10:29 AM, Kyley Harris [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Transaction Isolation keeps changing (my mind that is). Trying to work out what is best. I was trying snapshot, so that it mimics interbase. I may well just leave it on read/committed. This is one of those subject where I use Prayer as a means of making it work :) I also realized that the insert was the micro risk.. didn't know serialized would protect it. I'll try that and see. Thanks for the advise On Tue, Dec 2, 2008 at 8:08 AM, Neven MacEwan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Kyley Looks pretty robust (explicit locks), I've never used the output pseudotable structure (interesting, learn something new), If you set the TRANSACTION ISOLATION to SERIALIZED could you drop the explicit locks? and that would also mean that your insert was protected? There may be a SLIM chance, that if you have a very low TRAN ISO and you called GENGEN almost simultaneously WITH the same unititialized @TableName that the @@Rowcount for the second call would be incorrect (as there would be a race to insert) What is your TRANSACTION ISOLATION? Neven Where I am mainly using Sequences, not tied to a specific field is where many entities in our application all use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs. CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int AS SET NOCOUNT ON DECLARE @AVALUE Int BEGIN TRAN DECLARE @Res TABLE ( ID int ) BEGIN UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK) SET KEYVALUE = @[EMAIL PROTECTED] OUTPUT deleted.KEYVALUE INTO @RES(ID) WHERE KEYNAME = @TableName if @@ROWCOUNT = 0 begin SET @AVALUE = 1 INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1); end else begin SELECT @AVALUE=ID from @Res end END COMMIT TRAN RETURN @AVALUE
Re: [DUG] MSSQL Question
Kyley Why not just use a stored proc as a 'generator' or a high/low sequence, I take it that you want to know the surrogate PK Value? Must they be sequential? Neven pretend IDENTITY columns dont exist, because they don't do what I want, which is to create non-rollback numbers like IB Generators or Oracle Sequences. No matter how much rowlocking, updatelocks etc.. if a rollback happens then the Sequence numbers can get scrambled depending on transaction order. I've read under SQL 2008 (im using '05) that you can create a remote stored procedure using a loobback connection, where the remote call will then commit the increments outside of the local transaction running? Can anyone confirm this? My only other thought is to write a CLR (when I learn how) that uses critical sections and a seperate database connection to access and do all the commits to the Sequence generation table.. any thoughts comments appreciated. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Hi Neven, Currenlty I am using a stored proc that accesses a table in the form of ( SequenceName varchar(100); KeyValue Int ) I'm using update locks etc.. my worry is that Transaction A asks for ClientNO 1-3 keys Transaction B asks for ClientNO 4-6 keys A Rolls Back Transaction C or d or e in the future may get 4-6 taken by B again because of A's Rollback. The keys are not used for Primary or Relational anything. I use UniqueIdentifier for that.. The sequences do not need to be perfect, gaps are allowable.. these sequences are for Data Values used by the software users.. ie Batch No, Client No, Debtor No, Statement No, Invoice No.. etc etc. and these numbers are then used for quick referencing of information.. in interbase and Oracle.. its very easy.. have to pop out for an hour.. Thanks if anyone has any ideas for making sure I don't get lock contention, or reissuing of the same number twice. On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED] wrote: Kyley Why not just use a stored proc as a 'generator' or a high/low sequence, I take it that you want to know the surrogate PK Value? Must they be sequential? Neven pretend IDENTITY columns dont exist, because they don't do what I want, which is to create non-rollback numbers like IB Generators or Oracle Sequences. No matter how much rowlocking, updatelocks etc.. if a rollback happens then the Sequence numbers can get scrambled depending on transaction order. I've read under SQL 2008 (im using '05) that you can create a remote stored procedure using a loobback connection, where the remote call will then commit the increments outside of the local transaction running? Can anyone confirm this? My only other thought is to write a CLR (when I learn how) that uses critical sections and a seperate database connection to access and do all the commits to the Sequence generation table.. any thoughts comments appreciated. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
begin trans A begin trans B increment key value commit B -- don't care about trans A here it can be commited or roll backed Edward Koryagin --- On Mon, 1/12/08, Kyley Harris [EMAIL PROTECTED] wrote: From: Kyley Harris [EMAIL PROTECTED] Subject: Re: [DUG] MSSQL Question To: NZ Borland Developers Group - Delphi List delphi@delphi.org.nz Received: Monday, 1 December, 2008, 11:51 AM Hi Neven, Currenlty I am using a stored proc that accesses a table in the form of ( SequenceName varchar(100); KeyValue Int ) I'm using update locks etc.. my worry is that Transaction A asks for ClientNO 1-3 keys Transaction B asks for ClientNO 4-6 keys A Rolls Back Transaction C or d or e in the future may get 4-6 taken by B again because of A's Rollback. The keys are not used for Primary or Relational anything. I use UniqueIdentifier for that.. The sequences do not need to be perfect, gaps are allowable.. these sequences are for Data Values used by the software users.. ie Batch No, Client No, Debtor No, Statement No, Invoice No.. etc etc. and these numbers are then used for quick referencing of information.. in interbase and Oracle.. its very easy.. have to pop out for an hour.. Thanks if anyone has any ideas for making sure I don't get lock contention, or reissuing of the same number twice. On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED] wrote: Kyley Why not just use a stored proc as a 'generator' or a high/low sequence, I take it that you want to know the surrogate PK Value? Must they be sequential? Neven pretend IDENTITY columns dont exist, because they don't do what I want, which is to create non-rollback numbers like IB Generators or Oracle Sequences. No matter how much rowlocking, updatelocks etc.. if a rollback happens then the Sequence numbers can get scrambled depending on transaction order. I've read under SQL 2008 (im using '05) that you can create a remote stored procedure using a loobback connection, where the remote call will then commit the increments outside of the local transaction running? Can anyone confirm this? My only other thought is to write a CLR (when I learn how) that uses critical sections and a seperate database connection to access and do all the commits to the Sequence generation table.. any thoughts comments appreciated. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe Easy recipes for Christmas entertaining on Yahoo!Xtra Lifestyle- http://nz.lifestyle.yahoo.com/food-recipes ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
Hi Neven,
Currenlty I am using a stored proc that accesses a table in the form of
(
SequenceName varchar(100);
KeyValue Int
)
I'm using update locks etc..
my worry is that
Transaction A asks for ClientNO 1-3 keys
Transaction B asks for ClientNO 4-6 keys
A Rolls Back
Transaction C or d or e in the future may get 4-6 taken by B again
because of A's Rollback.
The keys are not used for Primary or Relational anything. I use
UniqueIdentifier for that.. The sequences do not need to be perfect,
gaps are allowable.. these sequences are for Data Values used by the
software users.. ie Batch No, Client No, Debtor No, Statement No,
Invoice No.. etc etc. and these numbers are then used for quick
referencing of information..
in interbase and Oracle.. its very easy..
have to pop out for an hour.. Thanks if anyone has any ideas for
making sure I don't get lock contention, or reissuing of the same
number twice.
On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Kyley
Why not just use a stored proc as a 'generator' or a high/low
sequence,
I take it that you want to know the surrogate PK Value?
Must they be sequential?
Neven
pretend IDENTITY columns dont exist, because they don't do what I
want, which is to create non-rollback numbers like IB Generators or
Oracle Sequences.
No matter how much rowlocking, updatelocks etc.. if a rollback
happens
then the Sequence numbers can get scrambled depending on transaction
order.
I've read under SQL 2008 (im using '05) that you can create a remote
stored procedure using a loobback connection, where the remote call
will then commit the increments outside of the local transaction
running? Can anyone confirm this?
My only other thought is to write a CLR (when I learn how) that uses
critical sections and a seperate database connection to access
and do
all the commits to the Sequence generation table..
any thoughts comments appreciated.
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] with Subject: unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] with Subject: unsubscribe
--
Kyley Harris
Harris Software
+64-21-671-821
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Thanks Neven,
I think your idea of keeping the key fetching in a seperate transaction is
the most likely to prevent any locks happening. Key fetch speed is not a
huge issue. This is very easy to implement in my middle layer. My issue is
then that I have a few triggers that insert data that call on a sequence
number.. if the server code that initiated this rollsback a transaction,I
guess my worst case scenrio is simply a deadlock on a table for a second or
so..
Thanks.
Now admittedly, I am just trying to be sure, because I'm still not familiar
with the in's and outs of MSSQL and how it locks.
Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part
of software failure to protect the database. Our average transaction time is
less than 100ms, generally about 5-10ms as we pre-do everything in OO and
then commit at the end of each area in a short fast swoop in a middle tier.
This tends to eliminate lock contention, I just want to cover all the bases
and make sure the keys don't rollback.
On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan [EMAIL PROTECTED] wrote:
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I
would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
Hi Neven,
Currenlty I am using a stored proc that accesses a table in the form of
(
SequenceName varchar(100);
KeyValue Int
)
I'm using update locks etc..
my worry is that
Transaction A asks for ClientNO 1-3 keys
Transaction B asks for ClientNO 4-6 keys
A Rolls Back
Transaction C or d or e in the future may get 4-6 taken by B again
because of A's Rollback.
The keys are not used for Primary or Relational anything. I use
UniqueIdentifier for that.. The sequences do not need to be perfect,
gaps are allowable.. these sequences are for Data Values used by the
software users.. ie Batch No, Client No, Debtor No, Statement No,
Invoice No.. etc etc. and these numbers are then used for quick
referencing of information..
in interbase and Oracle.. its very easy..
have to pop out for an hour.. Thanks if anyone has any ideas for
making sure I don't get lock contention, or reissuing of the same
number twice.
On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Kyley
Why not just use a stored proc as a 'generator' or a high/low
sequence,
I take it that you want to know the surrogate PK Value?
Must they be sequential?
Neven
pretend IDENTITY columns dont exist, because they don't do what I
want, which is to create non-rollback numbers like IB Generators or
Oracle Sequences.
No matter how much rowlocking, updatelocks etc.. if a rollback
happens
then the Sequence numbers can get scrambled depending on
transaction
order.
I've read under SQL 2008 (im using '05) that you can create a
remote
stored procedure using a loobback connection, where the remote call
will then commit the increments outside of the local transaction
running? Can anyone confirm this?
My only other thought is to write a CLR (when I learn how) that
uses
critical sections and a seperate database connection to access
and do
all the commits to the Sequence generation table..
any thoughts comments appreciated.
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] with Subject: unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz mailto:delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Re: [DUG] MSSQL Question
With MS SQL, only the top level commit/rollback matters, so to get real
sequential numbers you would have to do this in a initial
call/transaction to the db, before you perform the rest of the updates
in a second transaction, else in case of a rollback, the allocate key
update would also be rolledback.
The nested transactions in SQL are basically present to handle nested
stored procedure calls that deal with transactions.
Myles.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Kyley Harris
Sent: Monday, 1 December 2008 3:17 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL Question
Thanks Neven,
I think your idea of keeping the key fetching in a seperate transaction
is the most likely to prevent any locks happening. Key fetch speed is
not a huge issue. This is very easy to implement in my middle layer. My
issue is then that I have a few triggers that insert data that call on a
sequence number.. if the server code that initiated this rollsback a
transaction,I guess my worst case scenrio is simply a deadlock on a
table for a second or so..
Thanks.
Now admittedly, I am just trying to be sure, because I'm still not
familiar with the in's and outs of MSSQL and how it locks.
Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as
part of software failure to protect the database. Our average
transaction time is less than 100ms, generally about 5-10ms as we pre-do
everything in OO and then commit at the end of each area in a short fast
swoop in a middle tier. This tends to eliminate lock contention, I just
want to cover all the bases and make sure the keys don't rollback.
On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan [EMAIL PROTECTED] wrote:
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and
order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I
would
if (x = Begin trans, Generate statements, null keys, commits == commit)
{
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
Hi Neven,
Currenlty I am using a stored proc that accesses a table in the form
of
(
SequenceName varchar(100);
KeyValue Int
)
I'm using update locks etc..
my worry is that
Transaction A asks for ClientNO 1-3 keys
Transaction B asks for ClientNO 4-6 keys
A Rolls Back
Transaction C or d or e in the future may get 4-6 taken by B again
because of A's Rollback.
The keys are not used for Primary or Relational anything. I use
UniqueIdentifier for that.. The sequences do not need to be perfect,
gaps are allowable.. these sequences are for Data Values used by the
software users.. ie Batch No, Client No, Debtor No, Statement No,
Invoice No.. etc etc. and these numbers are then used for quick
referencing of information..
in interbase and Oracle.. its very easy..
have to pop out for an hour.. Thanks if anyone has any ideas for
making sure I don't get lock contention, or reissuing of the same
number twice.
On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Kyley
Why not just use a stored proc as a 'generator' or a high/low
sequence,
I take it that you want to know the surrogate PK Value?
Must they be sequential?
Neven
pretend IDENTITY columns dont exist, because they don't do what
I
want, which is to create non-rollback numbers like IB Generators
or
Oracle Sequences.
No matter how much rowlocking, updatelocks etc.. if a rollback
happens
then the Sequence numbers can get scrambled depending on
transaction
order.
I've read under SQL 2008 (im using '05) that you can create a
remote
stored procedure using a loobback connection, where the remote
call
will then commit the increments outside of the local transaction
running? Can anyone confirm this?
My only other thought is to write a CLR (when I learn how) that
uses
critical sections and a seperate database connection to access
and do
all the commits to the Sequence generation table..
any thoughts comments appreciated
Re: [DUG] MSSQL Question
Yes, I finally worked that out. Oracle allows you to start an independant
embedded transaction to specifically tailor for this type of task. I hope
MSSQL catches up on some of these features.
One Other thought.. if there are 2 databases sitting on the same server. Can
I access a table on one db and force it to commit independantly of the
database I am talking with? or do they sit in the same transaction too..
On Mon, Dec 1, 2008 at 3:36 PM, Myles Penlington [EMAIL PROTECTED] wrote:
With MS SQL, only the top level commit/rollback matters, so to get real
sequential numbers you would have to do this in a initial call/transaction
to the db, before you perform the rest of the updates in a second
transaction, else in case of a rollback, the allocate key update would also
be rolledback.
The nested transactions in SQL are basically present to handle nested
stored procedure calls that deal with transactions.
Myles.
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
*On Behalf Of *Kyley Harris
*Sent:* Monday, 1 December 2008 3:17 p.m.
*To:* NZ Borland Developers Group - Delphi List
*Subject:* Re: [DUG] MSSQL Question
Thanks Neven,
I think your idea of keeping the key fetching in a seperate transaction is
the most likely to prevent any locks happening. Key fetch speed is not a
huge issue. This is very easy to implement in my middle layer. My issue is
then that I have a few triggers that insert data that call on a sequence
number.. if the server code that initiated this rollsback a transaction,I
guess my worst case scenrio is simply a deadlock on a table for a second or
so..
Thanks.
Now admittedly, I am just trying to be sure, because I'm still not familiar
with the in's and outs of MSSQL and how it locks.
Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part
of software failure to protect the database. Our average transaction time is
less than 100ms, generally about 5-10ms as we pre-do everything in OO and
then commit at the end of each area in a short fast swoop in a middle tier.
This tends to eliminate lock contention, I just want to cover all the bases
and make sure the keys don't rollback.
On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan [EMAIL PROTECTED] wrote:
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I
would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
Hi Neven,
Currenlty I am using a stored proc that accesses a table in the form of
(
SequenceName varchar(100);
KeyValue Int
)
I'm using update locks etc..
my worry is that
Transaction A asks for ClientNO 1-3 keys
Transaction B asks for ClientNO 4-6 keys
A Rolls Back
Transaction C or d or e in the future may get 4-6 taken by B again
because of A's Rollback.
The keys are not used for Primary or Relational anything. I use
UniqueIdentifier for that.. The sequences do not need to be perfect,
gaps are allowable.. these sequences are for Data Values used by the
software users.. ie Batch No, Client No, Debtor No, Statement No,
Invoice No.. etc etc. and these numbers are then used for quick
referencing of information..
in interbase and Oracle.. its very easy..
have to pop out for an hour.. Thanks if anyone has any ideas for
making sure I don't get lock contention, or reissuing of the same
number twice.
On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Kyley
Why not just use a stored proc as a 'generator' or a high/low
sequence,
I take it that you want to know the surrogate PK Value?
Must they be sequential?
Neven
pretend IDENTITY columns dont exist, because they don't do what I
want, which is to create non-rollback numbers like IB Generators or
Oracle Sequences.
No matter how much rowlocking, updatelocks etc.. if a rollback
happens
then the Sequence numbers can get scrambled depending on
transaction
order.
I've read under SQL
Re: [DUG] MSSQL Question
Kyley Thanks Neven, Cheers I think your idea of keeping the key fetching in a seperate transaction is the most likely to prevent any locks happening. Key fetch speed is not a huge issue. This is very easy to implement in my middle layer. My issue is then that I have a few triggers that insert data that call on a sequence number.. if the server code that initiated this rollsback a transaction,I guess my worst case scenrio is simply a deadlock on a table for a second or so.. Thanks. Now admittedly, I am just trying to be sure, because I'm still not familiar with the in's and outs of MSSQL and how it locks. Funny how we obsess over the details isn't it Read the manual on TRANSACTION ISOLATION, it will give you a good idea Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part of software failure to protect the database. Our average transaction time is less than 100ms, generally about 5-10ms as we pre-do everything in OO and then commit at the end of each area in a short fast swoop in a middle tier. This tends to eliminate lock contention, I just want to cover all the bases and make sure the keys don't rollback. I don't think you'd get a deadlock for a second or two its more deterministic than that, if you do get a locked resource (which can only really happen if in a trans a locks table a and then table b and trans b locks them in reverse order, and they collide) then the second trans fails I tend to make my databases transactional (with accruals in triggers) and then you a) dont really require transactions and b) eliminate the chances of a deadlock as the table update sequence is controlled by the triggers and hence is consistent Neven ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Note also that once the first transaction gets its next key value and
updates the key table that should mean that the second transaction will get
blocked and is unable to get its next key value until the first has either
committed or rolled back to release the lock on the key table. This is fine
so long as each transaction is fast. However if your transactions are slow
(or some may be slow) then this could be a problem in which case
pre-fetching keys for all the records you want to insert is a good idea.
The Deadlock problem can occur if you aren't careful. For example if this
happens then you have a problem:
Tran 1:
GetNextKey('Client')
GetNextKey('Invoice')
Tran 2:
GetNextKey('Invoice')
GetNextKey('Client')
If both of these transactions manage to get their first keys at the same
time and then go to get their second key values... you have deadlock. #1
wants an Invoice ID but can't get because #2 has it locked, and #2 wants a
Client ID but can't get it because #1 has it locked.
Pre-fetching key values is an easy way around this. The other solution is to
always get your key values in the same order (ie always get Client before
Invoice) so that the locks are always applied in the same order and only one
transaction can be winning at any point in time.
Of course you may well already be aware of all this but it seemed pertinent
to the discussion.
Cheers,
David.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Myles Penlington
Sent: Monday, 1 December 2008 3:36 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL Question
With MS SQL, only the top level commit/rollback matters, so to get real
sequential numbers you would have to do this in a initial call/transaction
to the db, before you perform the rest of the updates in a second
transaction, else in case of a rollback, the allocate key update would also
be rolledback.
The nested transactions in SQL are basically present to handle nested stored
procedure calls that deal with transactions.
Myles.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Kyley Harris
Sent: Monday, 1 December 2008 3:17 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL Question
Thanks Neven,
I think your idea of keeping the key fetching in a seperate transaction is
the most likely to prevent any locks happening. Key fetch speed is not a
huge issue. This is very easy to implement in my middle layer. My issue is
then that I have a few triggers that insert data that call on a sequence
number.. if the server code that initiated this rollsback a transaction,I
guess my worst case scenrio is simply a deadlock on a table for a second or
so..
Thanks.
Now admittedly, I am just trying to be sure, because I'm still not familiar
with the in's and outs of MSSQL and how it locks.
Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part
of software failure to protect the database. Our average transaction time is
less than 100ms, generally about 5-10ms as we pre-do everything in OO and
then commit at the end of each area in a short fast swoop in a middle tier.
This tends to eliminate lock contention, I just want to cover all the bases
and make sure the keys don't rollback.
On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan [EMAIL PROTECTED] wrote:
Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
Hi Neven,
Currenlty I am using a stored proc that accesses a table in the form of
(
SequenceName varchar(100);
KeyValue Int
)
I'm using update locks etc..
my worry is that
Transaction A asks for ClientNO 1-3 keys
Transaction B asks for ClientNO 4-6 keys
A Rolls Back
Transaction C or d or e in the future may get 4-6 taken by B again
because of A's Rollback.
The keys are not used for Primary or Relational anything. I use
UniqueIdentifier for that.. The sequences do not need to be perfect,
gaps are allowable.. these sequences are for Data Values
Re: [DUG] MSSQL Question
Neven, didn't you read the manuals on multiple triggers? - I presume from your comment you do not have multiple triggers on the same table? You cannot really control the execution order (except for the first and last trigger), and it is randomised on different db's. I find it seems to rely on the DDL execution order in fact (but it does change with further trigger DDL on the same table). Myles. I tend to make my databases transactional (with accruals in triggers) and then you a) dont really require transactions and b) eliminate the chances of a deadlock as the table update sequence is controlled by the triggers and hence is consistent Neven Attention: This communication is confidential and may be legally privileged. If you are not the intended recipient, please do not use, disclose, copy or distribute it, other than to return it to us with your confirmation that it has been deleted from your system. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Myles I'd only ever have 2 triggers, one for RI (which reads only, hence does not fire other triggers) and the other for Data updates (which only spans the same related tables as the RI trigger), yes I know that other than that you cannot control their execution order. Cheers N Neven, didn't you read the manuals on multiple triggers? - I presume from your comment you do not have multiple triggers on the same table? You cannot really control the execution order (except for the first and last trigger), and it is randomised on different db's. I find it seems to rely on the DDL execution order in fact (but it does change with further trigger DDL on the same table). Myles. I tend to make my databases transactional (with accruals in triggers) and then you a) dont really require transactions and b) eliminate the chances of a deadlock as the table update sequence is controlled by the triggers and hence is consistent Neven Attention: This communication is confidential and may be legally privileged. If you are not the intended recipient, please do not use, disclose, copy or distribute it, other than to return it to us with your confirmation that it has been deleted from your system. ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice) John ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice) John ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] MSSQL Question
The main difference is that a Generator is more like an Oracle Sequence, It is table independant, which allows you to use the same sequence accross more than one table, and you can also have more than one per table. I find the identity is only useful if it is to be a primary key, not useful as a data element. On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan [EMAIL PROTECTED] wrote: John Really interbase generators are more akin to the mssql identity column type, the problem with mssql identity columns WAS with the @@Identity variable which was per connection (and hence triggers that inserted other identity columns not return the identity you would expect), this has been 'fixed' with the SCOPE_IDENTITY() function Stangely identity columns and generators behave the same way, ie they are both not rolled back by a trans PostgeSQL, uses a combo of both a column type of 'serial', which auto generates a 'sequence' Of course I could be spouting BS. Neven // Whats others opinion on the merits of trggers etc as discussed here vs the Firebird/Interbase method of using generators? I have often wondered if this is an area where Firebird/Interbase has a less convenient but more transparent mechanism - where you have to fire a generator to get a new key value before you start putting data in a new record. And the generator never rolls back even if the transaction does - avoiding the lock problem. Mostly in my experience this is fine, except some times when the ID numbers (eg batch numbers) are supposed to be strictly sequential and then you have to get the next value from a SQL query, and make sure no-one else is running another of the same batch at the same time. (In those situations the main thing that protects the database is that only one staff member ever runs this batch operation - I bet thats how a lot of databases run in practice) John ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe -- Kyley Harris Harris Software +64-21-671-821 ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
