Locking a record 4

[earthandfire]

I have a table with two fields type and number. I need to fetch the current value of number (for a given type) increment it and save it back into the table - returning the new number to the caller. However there could be several (10s or even 100s) of "simultaneous" requests for a new number.

I would like this to be handled by a Stored Procedure so that there is only one entry point to this table, but I can't see how to handle the locking.

BOL doesn't seem to help much and I've serched here but nothing seems to solve the problem.

Does anyone have any ideas?

Thanks.

 

[vongrunt]

If I understand correctly, the procedure is:

1) read counter value for specified type
2) increment it by one
3) save new value back

or

1) directly update value (SET counter=counter+1) for specified type
2) read saved value

And concurrent processes can interfere between 1) and 3) (first example) or 1) and 2) (second case)?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[earthandfire]

Either method would work. My concern is making sure the SP gets/updates and returns the new number before the next request is processed.

 

[vongrunt]

Wrap all above steps into small transaction - with at least isolation level 2 (repeatable read).



------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[earthandfire]

vongrunt, can I be totally thick and ask you to tanslate.

I'm still new to SQL Server and I haven't yet got involved with Transactions and have no idea what Isolation Level is.


Thanks again.

 

[fredericofonseca]

probably something like this will solve the problem.
Untested on production data!!!

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
CREATE PROCEDURE [dbo].[incnum] 
@newseq numeric out 
AS
begin transaction 
update numbers
set @newseq = seq = seq + 1
commit
GO

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[chrissie1]

So I was intrigued about this question and wrote up a quick test harnass with 3 threads in vb.net

Imports System.Data.SqlClient

Public Class Form1

    Dim results1 As New ArrayList
    Dim results2 As New ArrayList
    Dim results3 As New ArrayList
    Dim intfinished As Integer = 0

    Private Delegate Sub del_filltextbox(ByVal result As String)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        TextBox1.Text = ""
        intfinished = 0
        results1 = New ArrayList
        results2 = New ArrayList
        results3 = New ArrayList
        Dim thread1 As New Threading.Thread(AddressOf increasenumber1)
        Dim thread2 As New Threading.Thread(AddressOf increasenumber2)
        Dim thread3 As New Threading.Thread(AddressOf increasenumber3)
        thread1.Start()
        thread2.Start()
        thread3.Start()
    End Sub

    Private Sub filltextbox(ByVal result As String)
        If TextBox1.InvokeRequired Then
            Dim d As New del_filltextbox(AddressOf filltextbox)
            Me.Invoke(d, New Object() {result})
        Else
            intfinished += 1
            TextBox1.Text &= intfinished & " " & result & ControlChars.CrLf
        End If
    End Sub

    Private Sub increasenumber1()
        Dim con As New SqlConnection
        Dim adp As New SqlDataAdapter
        Dim com As New SqlCommand
        Dim dat As New DataTable
        con.ConnectionString = "Server=incctex;Database=general;Trusted_Connection=True;"
        con.Open()
        com.CommandType = CommandType.Text
        com.CommandText = "exec myfavoritesp 1"
        com.Connection = con
        adp.SelectCommand = com
        For inttemp As Integer = 0 To 10
            adp.Fill(dat)
            results1.Add("result:" & dat.Rows(0).Item(0).ToString & "  thread: 1")
        Next
        For inttemp As Integer = 0 To results1.Count - 1
            filltextbox(results1(inttemp).ToString)
        Next
    End Sub

    Private Sub increasenumber2()
        Dim con As New SqlConnection
        Dim adp As New SqlDataAdapter
        Dim com As New SqlCommand
        Dim dat As New DataTable
        con.ConnectionString = "Server=incctex;Database=general;Trusted_Connection=True;"
        con.Open()
        com.CommandType = CommandType.Text
        com.CommandText = "exec myfavoritesp 2"
        com.Connection = con
        adp.SelectCommand = com
        For inttemp As Integer = 0 To 10
            adp.Fill(dat)
            results2.Add("result:" & dat.Rows(0).Item(0).ToString & "  thread: 2")
        Next
        For inttemp As Integer = 0 To results2.Count - 1
            filltextbox(results2(inttemp).ToString)
        Next
    End Sub

    Private Sub increasenumber3()
        Dim con As New SqlConnection
        Dim adp As New SqlDataAdapter
        Dim com As New SqlCommand
        Dim dat As New DataTable
        con.ConnectionString = "Server=incctex;Database=general;Trusted_Connection=True;"
        con.Open()
        com.CommandType = CommandType.Text
        com.CommandText = "exec myfavoritesp 3"
        com.Connection = con
        adp.SelectCommand = com
        For inttemp As Integer = 0 To 10
            adp.Fill(dat)
            results3.Add("result:" & dat.Rows(0).Item(0).ToString & "  thread: 3")
        Next
        For inttemp As Integer = 0 To results3.Count - 1
            filltextbox(results3(inttemp).ToString)
        Next
    End Sub

End Class

myfavoritesp was this

CREATE PROCEDURE [dbo].[myfavoritesp] 
( @param int)
AS
UPDATE tbl_counter  SET counter = @param
select counter from tbl_counter
GO

needless to say that this didn't give the desired result.

second attempt with a little help from frederic/vongrunt

CREATE PROCEDURE [dbo].[myfavoritesp] 
( @param int)
AS
begin transaction 
UPDATE tbl_counter  SET counter = @param
select counter from tbl_counter
commit
GO

and I did not set the transaction level

Christiaan Baes
Belgium

"My new site" - Me

 

[chrissie1]

OOPS

and it gave the correct results.

Christiaan Baes
Belgium

"My new site" - Me

 

[SQLDenis]

also take a look at

WITH(UPDLOCK, HOLDLOCK)

in case you want more options



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[earthandfire]

Thanks all, stars all round. I'd almost reached the point of going back to Access where it was very easy . Thanks again

 

[vongrunt]

Let's simulate whole problem.

Take any sandbox database of your choice . Whenever you want to run test from below, run this code first:

if object_id('test', 'U') is null create table test (type int, counter int, insertedBy int)
truncate table test

if object_id('counters', 'U') is null create table counters( type int, counter int )
truncate table counters
insert into counters values(1, 0)

This sproc does mass inserts:

create proc testInserts( @maxrows int, @type int, @insertedBy int)
as
set nocount on
declare @counter int
declare @i int; set @i = 1

while @i <= @maxrows
begin
	update counters set counter = counter + 1 where type = @type
	WAITFOR DELAY '00:00:00.050'
	select @counter = counter from counters where type = @type

	insert into test (type, counter, insertedBy) values (@type, @counter, @insertedBy)
	set @i = @i + 1
end
go

WAITFOR simulates "real world" delay. Any lost UPDATE or nonrepeatable read will happen exactly during that delay.

Now, open two windows in QA and tile 'em vertically for quick access. Paste this into 1st window:

exec testInserts @type=1, @insertedBy = 7, @maxrows=100

... and this into 2nd window:

exec testInserts @type=1, @insertedBy = 42, @maxrows=100

Run both sproc calls at once (F5 - Ctrl-Tab - F5). Coffee time!

Finally, run this control query:

select count(*), count(distinct counter), sum(counter), (count(*)*(count(*)+1))/2
from test

If everything is OK then 1st and 2nd column will have the same value. Ditto for 3rd and 4th column.

Here is changed sproc that prevents concurrency problems:

alter proc testInserts( @maxrows int, @type int, @insertedBy int )
as
set nocount on
declare @counter int
declare @i int; set @i = 1

while @i <= @maxrows
begin
	[b]set transaction isolation level repeatable read
	begin tran[/b]
	update counters set counter = counter + 1 where type = @type
	WAITFOR DELAY '00:00:00.050'
	select @counter = counter from counters where type = @type
	[b]commit tran[/b]

	insert into test (type, counter, insertedBy) values (@type, @counter, @insertedBy)
	set @i = @i + 1
end
go

Any difference?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[chrissie1]

Bah, did that already with the threads via vb.net. Same thing.

without setting the isolation level

Christiaan Baes
Belgium

"My new site" - Me

 

[vongrunt]

> Bah, did that already with the threads via vb.net. Same thing.

Bah, VB muppets

> without setting the isolation level

True. Default level (read committed) is very likely enough in this case - final counter value is SELECTed only once.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why