Long transactions and locks with TableGenerator

[chawax]

OK. So this is the way we already do it. But we have lock problems with SQL Server and this is why I asked for help.

I investigated further with SQL Server profiler and it looks like each generator call is made within its own transaction (which is commited at each "generate" call). So it looks like problem doesn't deal with transactions. I really don't understand where this problems comes from

[chawax]

Hi,

I investigated further and it looks like the problem is a SQL Server one. It is due to the way SQL Server optimizer dynamically chooses lock granularity.

To understand what happens, I first tried to run the two following transactions in SQL Server Management Studio. Note the two requests make updates on different rows :

begin tran
update ID_GEN set sequence_next_hi_value=1 where sequence_name='CRENEAU_SEMAINE'

begin tran
update ID_GEN set sequence_next_hi_value=1 where sequence_name='PERIODE'

In this case I have a deadlock on the second request.

Then I tried similar request on an other table :

begin tran
update SITE set LIBELLE='test' where ID=1

begin tran
update SITE set LIBELLE='test' where ID=10

In this case there's no deadlock while the requests are very similar !

So it looks like SQL Server doesn't choose the same lock mode (either row, table or block) in the two cases. Maybe the algorithm takes into account the number of records in the table, the size of each record, etc ...

I could reduce number of deadlocks by creating an index on sequence_name column in ID_GEN table, but it doesn't fully solve the problem since I still have deadlocks sometimes (no more in SQL Server studio anyway, but still from Hibernate). From what I read the more reliable solution would be to add hints in SQL requests (something like "with (readpast)") but I am not sure about the consequences of this. And most of all I can't see any solution to tell Hibernate to add this in requests for id generator !

The only reliable workaround I found is to have one table for each id generator. I mean something like that :

@Id
@TableGenerator(name = "CreneauJourIdGenerator", table = "CRENEAU_JOUR_ID", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauJourIdGenerator")
@Column(name="ID")
private Long id;

@Id
@TableGenerator(name = "CreneauSemaineIdGenerator", table = "CRENEAU_SEMAINE_ID", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauSemaineIdGenerator")
@Column(name="ID")
private Long id;

I don't find it a great solution, but it works a least !

From all this, my conclusion is that if you want to use table generator on SQL Server, and if you have requests with a lot of insert requests (an so a lot of update requests on ids table), I would recommend to have one table for each id generator.

Any comment about this ?

[lokie]

Excellent Community. Thanks!


________________
Los Angeles wedding DJs